CODEWITHSUNDEEP
sqloracle-database
Abdulaziz Hasan Aldabbagh
Abdulaziz Hasan Aldabbagh

Reputation: 13

How are multiple case statements used in SQL?

I know this may be an easy question, but I've been stumped on this for the past hour and am not sure what terms to lookup that accurately describe what I am trying to do.

I am using application with Oracle 12C DB and i want to use multiple cases in this statement for the same field eg. INVC_TYPE= 0,2. First case: if its 2 then QTY*-1, and case no 2: if its 0 then regular and if its 0 then return.

So please there is anyone can help me:

select
t.ITEM_SID,
i.SBS_NO as INVC_SBS,
i.STORE_NO as INVCSTORENO,
s.STORE_NO as STORENO,
s.STORE_CODE as STORECODE,
s.STORE_NAME,
i.INVC_NO,
i.CREATED_DATE,
i.INVC_SID,
i.INVC_TYPE,
i.CASHIER_ID,
to_char(i.CREATED_DATE) as INVCDATE,
t.ORIG_PRICE as INVCORIGPRICE,
t.PRICE as INVCPRICE,
t.COST as INVCCOST,
case
when i.INVC_TYPE=2 then t.QTY*-1 
else t.QTY 
end as INVCQTY 
case when i.INVC_TYPE=0 then i.INVC_TYPE="REGULAR"
case when i.INVC_TYPE=2 then i.INVC_TYPE="RETURN"
else 'NA' 
end as INVCTYPE
from invoice i, invc_item t, SBS_STORE_LIST s
where 
INVC_TYPE in (0,2) and
i.invc_sid = t.invc_sid and
i.STORE_NO = s.STORE_NO and 
i.REF_INVC_SID is null;

Upvotes: 1

Views: 78

Answers (2)

Utsav
Utsav

Reputation: 8103

These are the issues with your code, to begin with.

  1. You are missing comma between 2 case statements.
  2. you are using case keyword twice in same column.
  3. In THEN part of case, you are assigning a value. You should just return a value

  4. Not an error but you should use proper join syntax 

       select t.ITEM_SID,
    i.SBS_NO as INVC_SBS,
    i.STORE_NO as INVCSTORENO,
    s.STORE_NO as STORENO,
    s.STORE_CODE as STORECODE,
    s.STORE_NAME,
    i.INVC_NO,
    i.CREATED_DATE,
    i.INVC_SID,
    i.INVC_TYPE,
    i.CASHIER_ID,
    to_char(i.CREATED_DATE) as INVCDATE,
    t.ORIG_PRICE as INVCORIGPRICE,
    t.PRICE as INVCPRICE,
    t.COST as INVCCOST,
    case 
        when i.INVC_TYPE = 2 
            then t.QTY * - 1
        else t.QTY
        end as INVCQTY,
    case i.INVC_TYPE
        when  0 then 'REGULAR'
        when  2 then 'RETURN'
        else 'NA'
        end as INVCTYPE
from invoice i
join invc_item t 
   on i.invc_sid = t.invc_sid
join SBS_STORE_LIST s 
   on i.STORE_NO = s.STORE_NO
where INVC_TYPE in (0, 2)
    and i.REF_INVC_SID is null;

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Try this:

select t.ITEM_SID,
    i.SBS_NO as INVC_SBS,
    i.STORE_NO as INVCSTORENO,
    s.STORE_NO as STORENO,
    s.STORE_CODE as STORECODE,
    s.STORE_NAME,
    i.INVC_NO,
    i.CREATED_DATE,
    i.INVC_SID,
    i.INVC_TYPE,
    i.CASHIER_ID,
    to_char(i.CREATED_DATE) as INVCDATE,
    t.ORIG_PRICE as INVCORIGPRICE,
    t.PRICE as INVCPRICE,
    t.COST as INVCCOST,
    case 
        when i.INVC_TYPE = 2 then t.QTY * - 1
        else t.QTY
        end as INVCQTY,
    case 
        when i.INVC_TYPE = 0 then i.INVC_TYPE = 'REGULAR'
        when i.INVC_TYPE = 2 then i.INVC_TYPE = 'RETURN'
        else 'NA'
        end as INVCTYPE
from invoice i
join invc_item t on i.invc_sid = t.invc_sid
join SBS_STORE_LIST s on i.STORE_NO = s.STORE_NO
where INVC_TYPE in (0, 2)
    and i.REF_INVC_SID is null;

Also, notice the explicit join syntax in place of comma based join. Always use this syntax as this is modern and clearer.

Upvotes: 1

Related Questions