Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: If statement within Select Query

    Hello,

    I want to write a query like select field1, if field1 != null or empty then 'Y' else 'N' from <tablename>.

    can anybody let me know how to go about this condition in Oracle SQL language.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Lookup the syntax of the CASE statement in the manual.

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    I hope this is what you mean

    select case cmta.assigned_on when (cmta.assigned_on != NULL) then 'Y' end from tableName cmta

    but it is giving me some error on != line

    can you show an example

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    NULLs are not compared using = or !=

    You have to use "IS NULL"

    Check out the manual regarding the handling of NULL values

  5. #5
    Join Date
    Sep 2007
    Posts
    56
    when i try to give this select case cmta.assigned_on when (cmta.assigned_on IS NULL) then 'N' end Assigned, from <tableName> cmta
    It gives me error i am using PL/SQL Developer it shows ORA-00900: invalid SQL statement.

    am i missing something here ?

    Also is then otherwise in the case statement like case <condition> when <test> then <value> when <test> then <value> otherwise <value> end.

    Let me know about this

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When you use the column name in the WHEN condition you may not use it in the CASE part.
    Check out the syntax diagram in the manual.

    Why do you think there is an "otherwise" part?
    Where did you get that information from?

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    or use a decode

    select decode(cmta.assigned_on,null,'N','Y') from ....
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Aug 2010
    Location
    Bangalore
    Posts
    7
    I hope this is what your looking for

    select field 1, (CASE WHEN ( field2 IS NOT NULL ) THEN 'Y' ELSE 'N' END) from table.

Posting Permissions

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