SQLite: Something like Oracle Decode or MS "If"
oracle, sql, sqlite
Solution
I think case is just as easy to use. It's familiarity with syntax that may be buggin you but this works just as well.
CASE Gender WHEN 'F' then 'FEMALE'
WHEN 'M' then 'MALE'
ELSE 'UNDEFINED'
END as GenderName
or
CASE WHEN GENDER = 'F' THEN 'FEMALE'
WHEN GENDER = 'M' THEN 'MALE'
ELSE 'UNDEFINED'
END as GenderName
It should be noted that CASE is pretty much db agnostic: Decode, iif, if, and other variations are not. So if your query needs to work on several different databases; stick to case as it's more likely to work across multiple database platforms.
With regards to performance & Why have both: SQL Server IIF vs CASE
I've read articles stating the generated execution plan is the same for both.
I've read articles stating the difference between the two is nano-seconds.
I've also read articles stating case is slightly faster because the engine basically switches the iif to a case; so iif has overhead.
I've read articles that state iif is faster because it's a binary check and is only ever going to return true or false (or null) so it has less overhead.
I've seen where you can only nest up to 10 for each type. I've also seen where someone indicated you can have 12 nested values...
I've seen some state if they need true/false result and it's a just a single comparison to only use IIF, if multiple, case.
To know which is better: testing is required for specific RDBMS/Version.
From a personal standpoint... I prefer CASE due to the multiple checks it supports and I personally find it easier to read. But if you just work in MSFT environment: you may find the inverse to be true.
Problem
In the past, I've used "decode" in Oracle, and "iif" in Microsoft, in the SELECT stmt of an SQL query. I've been considering SQLite for a project, but as far as I can tell, conditionals in the SELECT stmt are difficult. Googling brings up the use of CASE.. but intuitively, that seems that the performance would not be very good, not to mention more difficult to code. Or maybe I don't fully understand it. Thoughts or advice from anyone? I'd really like to use SQLite for this project.