oracle sql case statement question

  case when id.amount < 0 then id.order_qty*-1 when inv_product_type = 'POCRY' then 5
else id.order_qty end qty,
In the above statement I want to add if id.debit_credit = 'CR' when inv_product_type = 'POCRY' the -5
Any help appreciated
LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
your syntax isn't quite correct with the  if/when ,
  I assume you mean the two conditions should be connected with an "AND"


case
when id.amount < 0 then id.order_qty*-1
when id.debit_credit = 'CR' and inv_product_type = 'POCRY' then -5
when inv_product_type = 'POCRY' then 5
else id.order_qty end qty,
0
 
anumosesAuthor Commented:
when id.debit_credit = 'CR' and inv_product_type = 'POCRY' then -5
when inv_product_type = 'POCRY' then 5

is there a need to add
 id.debit_credit = 'DR' and inv_prod_type = 'POCRY' then 5
0
 
sdstuberCommented:
you tell me,  is there a need?

if debit_credit is NOT 'CR',  does that mean that IS 'DR'   (no nulls or other values?)

if so,  then adding an explicit check for DR is ok, but not needed.

if debit_credit might possibly be something other than CR or DR,  then adding the additional check is a good idea
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.