Oh, SQL itself is fairly easy. For many things, I'm a fan.
However, SQL query construction and result munging is painful.
Consider a UI search screen that has eight potential search parameters that requires a one to many join for the results. The query construction, under something like JDBC, can end up with hundreds of lines of tedious code, like (and this is a condensed example!):
String query = "SELECT a.*, b.* from table1 a inner join table2 b on a.fk_id = b.id where" // shorthand
List whereClauses = []
if (params.region) { whereClauses.add(getRegionWhereClause(params.region)) } //hope this doesn't require another join!)
if (params.country) { whereClauses.add(getCountryWhereClause(params.country)) }
...
if (params.lastParam) { whereClauses.add(getLastParamWhereClause(params.lastParam))}
query += whereClauses.join (" AND ")
...
Each of the params methods are going to have a few lines of code.
def getRegionWhereClause(regionCodeList) { "a.region IN" } //Hope you never want to change the table alias here!
http://use-the-index-luke.com/sql/where-clause/obfuscation/s... shows why using an ISNULL/NVL hackaround for static queries is the wrong answer.
Then you're going to return a list of rows that looks like
| a.1 | a.2 | b.1 | b.2 |
| a.1 | a.2 | b.1' | b.2' |
Where you really want:
| a.1 | a.2 | [[b.1 | b.2] | [b.1' | b.2']]
So you have to go through and munge it. (Can you use something like CONCAT as a hack and groupby? Sure, but that introduces other problems.)
Having a way to pass in optional parameters to a where clause for the DB to strip out (while staying performant) and being able to return a 1 to many as an array would solve many problems, but it doesn't fit the paradigm of SQL.
