Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    1

    Thumbs up Unanswered: writing query in then part of case statment

    hi

    is it possible to write a select query in 'then' part of case statment as below?
    I wanted to execute the appropriate select statment based on the condition.

    case
    when <condition> then <select query>
    else <Another select query>
    end

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    The embedded query is called a Scalar Full Select, and yes, it is allowed as a CASE expression.

    A scalar-fullselect as supported in an expression is a fullselect,
    enclosed in parentheses, that returns a single row consisting of a single
    column value. If the fullselect does not return a row, the result of the
    expression is the null value. If more than one row is to be returned for a
    scalar fullselect, an error occurs.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jsharon, i'm pretty sure that c_jai does not want "a single row consisting of a single column value"

    c_jai, could you please show both of your select queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use CASE statement(not CASE expression) in a compound SQL.
    But, if used SELECT statement(s) in a compound SQL, problem is how to get the result.

    If number of items and data types of them in the reslts of the two select statements were identical,
    you may want to use something like...
    <select query>
    WHERE <condition>
    UNION ALL
    <Another select query>
    WHERE CASE <condition> THEN 0 ELSE 1 END = 1

    If <condition> was always true or false(that means no unkown result)
    <select query>
    WHERE <condition>
    UNION ALL
    <Another select query>
    WHERE NOT <condition>

    If <select query> or <Another select query> include WHERE clause,
    put all the where conditions in parentheses then add "AND <condition>" or "AND NOT <condition>".
    Last edited by tonkuma; 05-17-11 at 10:16. Reason: Add last sentence.

Posting Permissions

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