Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Can I use a field from case statement without sub queries

    Hello

    I was wondering whether you can get a field name from a case statement and then reference that field without creating a sub query

    I.e

    CASE WHEN field a IS NOT NULL
    THEN field a
    ELSE field b
    END AS Code

    However I now want to reference the field established as Code and create another case when statement using this field

    I.e.

    CASE WHEN Code = 10X THEN "South"
    WHEN Code = 11A THEN "North"
    ELSE "Other"

    How can I reference the field "Code" with another case statement, can it be done without creating a subquery?

    Any advice is greatly appreciated

    Thanks
    Helen

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    With subquery factoring clause (the first link I stumbled upon), but hey, that's still a "subquery". Nothing else crosses my mind at the moment.

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thank you for this link, I really appreciate it

    Thanks
    Helen

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about using nested CASE expressions.

    Example 1:
    Code:
    CASE CASE
         WHEN field_a IS NOT NULL
         THEN field_a 
         ELSE field_b
         END
    WHEN '10X' THEN 'South'
    WHEN '11A' THEN 'North'
    ELSE 'Other'
    END
    Example 2:
    Code:
    CASE NVL(field_a , field_b)
    WHEN '10X' THEN 'South'
    WHEN '11A' THEN 'North'
    ELSE 'Other'
    END
    Though, the examples may not applicable for all the CASE expressions,
    they might work in this case.

Posting Permissions

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