By way of example, we will revisit our trusty Skype database and in particular the messages table and the type column. The picture below shows a query in four of the columns from this table and we can see that the type column is pretty unhelpful. We would like to make this a bit more user-friendly to present the report in court.
The SQLite user manual shows that a case expression has the following form:
CASE expr WHEN expr THEN expr WHEN expr ELSE expr END
A real-world example will hopefully make this clearer
CASE Messages.type WHEN 53 THEN ‘Contact Blocked’ WHEN 61 THEN ‘Text Message’ WHEN 30 THEN ‘Start Voice call’ ELSE Messages.type END AS MsgType,
The example above compares the Messages.Type field to each of the three supplied values in the WHEN part of the expression and if they match then they are replaced by the value in the THEN part of the expression which in our case are quoted string values. So 53 will be replaced by ‘Contact blocked’ 61 by ‘Text Message’ etc. (*you can supply more than 3 WHEN expressions).
The final ELSE part of the expression is important as it instructs SQLite what to do if a type value is found which isn’t coded into our expression. We could use ELSE ‘Unknown’ which would display unknown for every type value that isn’t 53, 61 or 30 but this potentially hides evidence so by using table.fieldname again we can display the actual value. The table below shows this more clearly.