Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Quick decode question

    Is it possible to use sub queries in a decode statement? Or is there a well know alternative?

    eg.

    I use decode in a Oracle reports select querry:

    And rs.location = decode(:location,'all',rs.location,:location)

    I would like to change this to be somthing like (don't ask why'):

    And rs.location in decode(:location,'all',rs.location,'sub', (select loc_num from special_locations), :location)

    Oracle errors ORA01427 - single-row subquery returns more than one row.

    Can any one suggest an alternative method?

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    Quote Originally Posted by chumpski
    And rs.location in decode(:location,'all',rs.location,'sub', (select loc_num from special_locations), :location)
    how about using "EXISTS" clause

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    use a CASE statement
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    OR, in your decode subquery you need to specify ONE value, not
    every row from a table
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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