Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: question about oracle case expression

    The following code, I would think, should return "SORRY," and yet it returns no rows.
    Why would that be?

    SELECT CASE
    WHEN flag IS NULL
    THEN ('SORRY')
    ELSE flag
    END
    FROM (SELECT 'Y' flag FROM SOME_TABLE WHERE 1=2);

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT 'Y' flag FROM dual WHERE 1=2
    SQL> /

    no rows selected
    ===============
    "no rows selected" is not NULL.

    Why are you mucking around with such perverse SQL?
    First make it (SQL) work, then make it fancy.
    My impression from your posts is that you can barely even spell SQL.
    I suggest you ask your instructor for assistance.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    let me rephrase my question, then: if the result set returned from the second select is empty, being that 1 !=2, why would it not return "sorry" since that's what the case expression is telling it to do

    now, my suspicion is because no rows is not the same as null, but if so, what's the best way to return null from the second select in the case where no rows are returned?

  4. #4
    Join Date
    Jan 2005
    Posts
    15
    I just re-read the non-insulting part of your post and saw that indeed no rows is not the same as null - so thanks for that answer. If so, how do I make it return null in that case.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If so, how do I make it return null in that case.
    You don't.

    RTFM

    http://download-west.oracle.com/docs...schem.htm#2852

    Nulls Indicate Absence of Value

    A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.

    NULL is NOT the same as no rows returned.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2005
    Posts
    15
    thanks,
    no, so I get that part now...
    what I am asking is that being that the return of "sorry" is contingent upon the flag being null, how do I get it so that "sorry" to be triggered where there are no rows returned rather than flag is null.

    I mean one way would be to make sorry not contingent upon null, but on no rows returned, but I don't know how I would do that. So that's why I asked about the other alternative, that of making flag null where no rows are returned.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    So you refuse to RTFM & want to be spoon fed.
    Go research "NVL" and/or "NOT EXISTS".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2005
    Posts
    15
    ok, I'll play with "not exists"
    as far as NVL is concerned, I am familiar with it, but I thought it's for handling null values substitution, whereas in this case I need to handle no rows exist, rather than null.

Posting Permissions

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