oracle sql case statement question

Posted on 2011-10-12
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
Question by:anumoses
    LVL 73

    Accepted Solution

    your syntax isn't quite correct with the  if/when ,
      I assume you mean the two conditions should be connected with an "AND"

    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,
    LVL 6

    Author Comment

    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
    LVL 73

    Expert Comment

    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
    LVL 6

    Author Closing Comment


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Read only access to a user 5 83
    Oracle to_char 21 52
    Number Format 1 31
    Add 0 to end of Number 21 58
    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now