#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    1
    Rep Power
    0

    Complex Case Expression


    Hello, I'm really new to writing SQL queries to query Oracle databases and could use a little assistance. I hope I'm in the correct forum.

    I have a case expression as follows:

    (CASE WHEN DATEa=DATEb THEN 0
    WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
    WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
    WHEN STATUS='PENDING' THEN NULL
    ELSE NULL
    END) AS RESULTa,

    Now what I need to be able to do is place those results in buckets, similar to this:


    (CASE WHEN RESULTa < 0 THEN '<0'
    WHEN RESULTa between -1 AND 6 THEN '<=5'
    WHEN RESULTa >5 THEN '>5'
    ELSE ''
    END) AS BUCKETa

    I understand that I can't call an alias from a previous case expresson to get these desired results and I'm confused on how I could combine the two statements to get the desired bucket.

    Any assistance or advice anyone could provide would be most appreciated.

    Thanks!

    tp
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    165
    Rep Power
    13
    You can use subselectes if you use that CASE in selects stetements or
    you can put first CASE statement in place of RESULTa in the second statement
    like that
    Code:
    (CASE 
    WHEN (CASE ... whole first case END) <0 THEN '<0'
    WHEN ....
    END) AS BUCKETa
    BTW two intervals you use in CASE statement are not disjoint I mean
    (inf,0) and <-1,-6>

IMN logo majestic logo threadwatch logo seochat tools logo