Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Unanswered: Deriving data from nothing?

    Hi all,
    I did something like this and it gave me the result as 123456.please look at this and tell me how does something be derived from a blank table(set)?
    Code:
      create table a ( id integer);
      
      select case when 1 = 1 
        then 123456
        else count(*) end as NUMBER
        from a
        where 1 = 0;
    I tried this on mysql and one of my friends -who had told me about this- says it works same on sql server.
    PM: My friend said that he saw this in one of Joe Celko's columns but I could not find that column.
    -Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is one of those things that doesn't make any sense at first, but once you work through it, it makes perfect sense.

    The use of Count(*) within the column list implies grouping. That forces a result set row to be returned at the aggregate level. The WHERE clause is evaluated after the (degenerate case) join, and filters out any rows that might be in the table. After the aggregation, the CASE is evaluated and the result is posted.

    Funky, but it does make sense!

    -PatP

Posting Permissions

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