[ebiester]

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.