One thing I discovered recently was the power of the SQL CASE statement. I had to do multiple joins on multiple tables and then proceed to select data from a certain field that can actually be a different field depending on certain circumstances, as one unified field. Sounds fun, right?

One way you can figure out which field to use is to return them all and take care of the display logic on the Coldfusion side of things. The problem I had with doing this was the query I was building was probably going to be used in multiple places and I didn't want to tote that Coldfusion logic everywhere when I could just handle it in the query itself.

The first thing I learned was that apparently you cannot use SQL CASE statements to decide which tables to join on. What I have concluded you do is join all the tables you need to, then use the CASE statements to figure out which fields to select or filter on.

This leads me into why I am now a big fan of CASE statements in SQL - you now have unified data to present across the pages.

I am going to present some examples of how to work with CASE statements Here is an example of the type of data I would be dealing with here. We have 5 tables:

  • Media, which contains the id of the next 3 tables, the id of the type of media and any other data that is used across the next 3 tables.
  • Videos, which contains a video_id and any fields that are specific to videos in general
  • Articles, which contains an article_id and any fields that are specific to articles in general
  • Audio, which contains an audio_id and again, any fields specific to audio files
  • Media_type_table_data, which contains metadata about tables of the 3 specific media types (the media_type_id, table name, some column names, etc). This table is only used in the last example

Here is an example of how a simple CASE statement works:

view plain print about
1CASE media.media_type_id
2 WHEN 1 THEN 'This is a video'
3 WHEN 2 THEN 'This is an article'
4 WHEN 3 THEN 'This is an audio file'
5 ELSE 'This is not a valid media type'
6 END

In the above example, we are doing our decision making based on one field of the main query (media.media_type). But, we can also do a CASE statement based on a combination of fields from the main query:

view plain print about
1CASE
2 WHEN media.active = 'false' THEN 'This media is not active'
3 WHEN media.drop_date < getDate() THEN 'This media is expired '
4 WHEN media.media_type_id = '' THEN 'This is not a valid media type'
5 ELSE 'This is good media'
6 END

Here is a big one that shows multiple case statements in action. Note how I am using them in the SELECT and WHERE clauses:

view plain print about
1SELECT m.media_id, m.media_type_id
2title =
3CASE m.media_type_id
4WHEN 1 THEN t1.video_name
5WHEN 2 THEN t2.article_title
6WHEN 3 THEN t3.audio_name
7END,
8release_date =
9CASE m.media_type_id
10WHEN 1 THEN t1.released_date
11WHEN 2 THEN t2.published_date
12WHEN 3 THEN t3.released_date
13END
14FROM media m
15LEFT OUTER JOIN videos t1 ON m.media_id = t1.video_id
16LEFT OUTER JOIN articles t2 ON m.media_id = t2.article_id
17LEFT OUTER JOIN audio t3 ON m.media_id = t3.audio_id
18WHERE
19media.active = 'true'
20AND
21m.media_type_id IN (1,2,3)
22AND
23CASE m.media_type_id
24WHEN 1 THEN t1.video_name
25WHEN 2 THEN t2.article_title
26WHEN 3 THEN t3.audio_name
27END
28IS NOT NULL
29AND
30CASE m.media_type
31WHEN 1 THEN t1.director
32WHEN 2 THEN t2.author
33WHEN 3 THEN t3.narrator
34END
35LIKE '%martin%'

Notice how in the Select clause, I put the case statement after the equals sign (I could also ditch the equals sign and what is to the left and after the CASE put AS new_field_name). But, in the WHERE clause, I put it before the equals sign. Since I am using the case statements to figure out what actual fields to deal with, I am just placing it where I would normally place the field name.

And, CASE statements are so versatile! They can be nested, you can put coldfusion decisions within a case statement and so much more!

One thing that I did (and I think the root of why I fell in love with SQL Case statements) was loop through queries that contained metadata for the joined tables on my main query:

view plain print about
1CASE d.searchable_object_id
2<cfloop query="qMediaTypes">
3WHEN '#qMediaTypes.media_type_type#' THEN
4<cfif len(qMediaTypes.media_type_author_column_name)>t#qMediaTypes.media_type_id#.#qMediaTypes.media_type_author_column_name#<cfelse>'This media type does not have an Author Field'</cfif>
5</cfloop>
6END

One error that I ran into a few times was "Incorrect syntax near the keyword 'WHEN'". Many times, this was because either I didn't put an else on my Coldfusion decision or my Sql CASE statment; apparently SQL doesn't like when it doesn't know what data it should grab! Also, I noticed that it didn't like when I put '' in my else statements (I think this is for the same reason).