# Tuesday, April 29, 2008

Cases in SQL syntax

I've never seen this SQL syntax:

    select
        column1,
        case
            when table1.column1 is not null then 1
            when table2.column1 is not null then 2
            when table0.column1 is not null then 0
        end as column_nameX,
    ...

Have you?

#    Comments [2] | Trackback
Tuesday, April 29, 2008 7:21:15 PM UTC
yes, I have
and I use it when appropriate. Your example is not very good but this could be used widely in views to facilitate data understanding. For instance if you have several bit fields for describe why some operation failed you could add calculated column with IF statement of CASE statement to present a better result.
Of course this is not very good in large databases because filtering by this column will cause full table scan
Galcho
Wednesday, April 30, 2008 3:06:19 PM UTC
Yea, it looks like it could get ugly.

I'm wondering why didn't we study it our Database course in the University.
mihail.stoynov
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, blockquote@cite, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview