DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Is there anyway to have an IF statement?

  1. #1
    Join Date
    Oct 2000
    Posts
    9

    Question

    Is there an IF statement that I can use in an Oracle SQL statement.

    Example:

    SELECT
    item,
    description,
    Week1 = If orderdate between A and B Then qty, else 0
    Week2 = If orderdate between C and D then qty, else 0
    and so forth...
    FROM
    orders;

    Any help would be greatly appreciated...

    Thank you

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    There's DECODE, so it would be

    SELECT
    item,
    description,
    decode(orderdate, a, qty, b, qty, 0) week1,
    decode(orderdate, c, qty, d, qty, 0) week2,
    and so forth...
    FROM
    orders;

    As you can see, it can't do ranges, it is a one to one, then an others value.

    Is this what you want ?

  3. #3
    Join Date
    Oct 2000
    Posts
    1
    The dcode() function is good only if you have predefined values to check against, it can't help in range comparisions.
    What you need is a UNION to join two SQL statements like this:

    SELECT item, description, qty as wk_name
    FROM orders
    where orderdate between A and B
    UNION
    SELECT item, description, 0 as wk_name
    FROM orders
    where orderdate not between A and B
    ;

    Hope this helps.


  4. #4
    Join Date
    Oct 2000
    Posts
    9
    Well a Union would be nice, but really i am looking to having several columns
    7 weeks
    and this is a VERY large table, so 7 queries unioned together would be VERY large indeed.

    I can't believe there is no logic commands besides strict = in Oracle SQL

    I think this is a HUGE disadvantage

    any suggestions?

  5. #5
    Join Date
    Oct 2000
    Posts
    90
    You're right, that is probably the only way you can do it, but there are likely to be major performance implicants .......... it could run like a dog :-)

  6. #6
    Join Date
    Oct 2000
    Posts
    123

    Thumbs up

    Two ways:

    1. Using decode and sign at same time, this two functions are existed in diferent versions.

    2. If U are in latest version, probably U can use "case" function. Lookup the corresponding docus for the usage.

    Take care

  7. #7
    Join Date
    Oct 2000
    Posts
    9
    I am using Oracle 8.0.4.4.0

    And what do you mean use Decode and Sign at the same time?

  8. #8
    Join Date
    Oct 2000
    Posts
    123

    Smile

    Hi,

    "Sign" is a function which indicating the value > 0, = 0, and <0, it becomes more powerful in conjunction with "decode" function.

    P.l. check "The complete reference, ORACLE 8" P418, hope it will give you a hint.

    Take Care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width