?
Solved

oracle sql case statement question

Posted on 2011-10-12
4
Medium Priority
?
320 Views
Last Modified: 2012-05-12
  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
0
Comment
Question by:anumoses
  • 2
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36957155
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
 
LVL 6

Author Comment

by:anumoses
ID: 36957580
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36957657
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36958098
thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question