x

SQL Case Statement issue-SQL 2nd case statement based on 1st case statement

I'm trying to create a sql query by which the 2nd and 3rd case statement must be based on the 1st case statement .

My question is can we create 2nd and 3rd case using the result from 1st case statement in a single query.

Below is my query,when i run it,for example my uompointer=1800,my cq=1800,then based on the 2nd case statement,my arstat support to be Complete, but it display Partial.

                    select a.*,
                          case
                               when a.articleno='B0-015'then substring_index(a.qty*1,'+',1)
                               when a.articleno='B0-001'then substring_index(a.qty*1,'+',1)                               
                               when a.articleno='01-035' then substring_index(a.qty*2,'+',1)
                         else substring_index(a.qty*e.uompointer,'+',1)
                     end uompointer,
                            case
                               when ifnull(b.cq,0) = 0 then '-'
                               when ((uompointer) - ifnull(b.cq,0)) <= 0 then 'Complete'
                               else 'Partial'
                            end arstat,
                case
                   when ((uompointer) - ifnull(b.cq,0)) <= 0 then b.enddate
                   else 'TBA'
                end enddate,

                                from schedule as a
                                  left join (
                                     select idschedule, sum(inflate) as cq, min(date(logDate)) as startdate, max(date(logDate)) as enddate
                                     from log
                                     where isvalid = '1'
                                     group by idschedule

                 left join product as e on(a.articleno=e.articleno)
                 where a.isvalid = '1'
                order by a.shipdate

Can anybody assist me on this error?

more ▼

asked Dec 04 at 08:15 AM in Default

avatar image

VarEcheng
21 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It is possible, you need to nest the cases

like the following

 CASE
         WHEN (p.product_sale_price_status = 'Y')
         THEN <-- add this
             CASE
             WHEN (po.option_upcharge IS NOT NULL)
                 THEN (sc.product_qty * (p.product_sale_price + po.option_upcharge)) 
                 ELSE (sc.product_qty * p.product_sale_price)    
             END
         ELSE
             CASE
             WHEN (po.option_upchage IS NOT NULL)
                 THEN (sc.product_qty * (p.product_price + po.option_upcharge))
                 ELSE (sc.product_qty * p.product_price)
             END
         END AS product_total
 
more ▼

answered Dec 04 at 09:58 AM

avatar image

anthony.green
3.1k 1 4 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1154
x118

asked: Dec 04 at 08:15 AM

Seen: 22 times

Last Updated: Dec 04 at 09:58 AM

Copyright 2017 Redgate Software. Privacy Policy