CASE statements will perform fine on most SQL RDBMS engines. It is much better to have these rules in a view or stored procedure than embedded in client code. Even better, create Rules tables that treat the metadata as data and use JOINs in place of CASE statements.
I have tried to avoid speaking in terms of absolutes, but here is one that comes close:
Almost never embed SQL queries that call dedicated back end database server objects in modules of an imperative language like JAVA.
Well, there's an irony there. If we set aside performance issues for a moment, the advantage of moving the conditional evaluation from a "hard coded" CASE expression to "rules tables" (I'd probably call that "control tables" personally) is that you can then conveniently change this by editing the data instead of recoding.
Except, often the editing of that data requires building more infrastructure and arranging access rights, whereas once the CASE expression is in a view or stored procedure, usually whoever had access to write the view or stored procedure can readily replace it - even (gasp) in production.
Personally I'm with you though, my ideal is to write/build analytical systems where the code (SQL) is comprehensive and settled, and all behaviour modification is done by editing control data. Alas I usually seem to be alone in that ambition.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
CASE statements will perform fine on most SQL RDBMS engines. It is much better to have these rules in a view or stored procedure than embedded in client code. Even better, create Rules tables that treat the metadata as data and use JOINs in place of CASE statements.
I have tried to avoid speaking in terms of absolutes, but here is one that comes close:
Almost never embed SQL queries that call dedicated back end database server objects in modules of an imperative language like JAVA.
Well, there's an irony there. If we set aside performance issues for a moment, the advantage of moving the conditional evaluation from a "hard coded" CASE expression to "rules tables" (I'd probably call that "control tables" personally) is that you can then conveniently change this by editing the data instead of recoding.
Except, often the editing of that data requires building more infrastructure and arranging access rights, whereas once the CASE expression is in a view or stored procedure, usually whoever had access to write the view or stored procedure can readily replace it - even (gasp) in production.
Personally I'm with you though, my ideal is to write/build analytical systems where the code (SQL) is comprehensive and settled, and all behaviour modification is done by editing control data. Alas I usually seem to be alone in that ambition.