As a follow-up to this question, I have one of my own.
The original question concerns the use of a
CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously the SQL is pretty hairy. The OP's question concerned SQL Server 2012, my question, however, is about PostgreSQL.
In my answer, I proposed the use of a
VIEW as a "one-stop-shop" solution - i.e. declare the
VIEW once, use it anywhere - and this applies for any query in the future also and any variant thereof.
So, the OP now has 5 options:
I excluded the option of changing the underlying data since, frequently in this forum, consultants/DBA's/programmers are not allowed change underlying data - makes the answers more interesting also!
CASE expression with > 100 options (x4) is horribly cumbersome and complex - but when is it a good idea to use
CASE and at what point does it become a minus rather than a plus?
In my opinion (and not just because it's my answer!), a
VIEW is the optimal solution - it's simple, will work for all RDBMS's and is permanent and will work for all queries now and into the future should the OP wish to modify the query.
JOIN LATERAL construct will work also as a kind of derived table, which is pretty much what a
CTE is also. They can both be used down the line in the same query.
My question is: Which of the 4 approaches is better/best and at what point does the technical (ease of use, speed, query plan optimisation) tilt in the particular solution's favour?
I know this is sort of an "opinion" question, but it's one very firmly rooted in technical imperatives, not hand-waving. It's maybe a bit like asking under which circumstances is a "nested loop", "sort merge" or "hash"
JOIN optimal (or
Index scan). It is not a "what colour should my database be" type of question!