Hi Roger,
2013/6/24 Roger Thomas <[email protected]> > I guess it depends on how you define GREATEST() > > I define GREATEST() as follows (seem to be how Oracle defines it, but I > may have missed something) > > - Any NULL value received in any parameter means a NULL value returned > - Comparisions are done with basic > rather than via a collating table > (which is how MAX() works). > - You are looking for the GREATEST value from a range of single value > columns, not some form of aggregation. Yes, although I guess that with lots of arguments to GREATEST(), the MAX() variant might be better: GREATEST(a, b, c, d, e) = SELECT MAX(t.v) FROM ( SELECT a UNION ALL SELECT b UNION ALL SELECT c UNION ALL SELECT d UNION ALL SELECT e ) t(v) This might work if the subquery does not obscure the scope of a, b, c, d, e. > Doing some testing via a simple SQLite shell the following provides a > solution for GREATEST(V1,V2,V3) > > SELECT CASE WHEN (V1 IS NULL OR v2 IS NULL OR V3 IS > NULL) -- any NULLs and we can just return a NULL > THEN > NULL > ELSE > CASE WHEN V1 > (CASE WHEN V2 > V3 THEN V2 ELSE V3 END) > -- Is V1 > than the result returned from finding the > of V2 or V3 > THEN > V1 > ELSE > (CASE WHEN V2 > V3 THEN V2 ELSE V3 > END) -- As V1 was not > then we return the result from a V2 > and V3 check > END > END AS result > FROM > ( > SELECT 'Roger' as V1, 'Ian' as V2, 'Thomas' as V3 > ) AS results > > For each supported parameter list length a different size query will be > required. So for parameter lists of 1,2 or 3 its simple and short, after > that the cascade of cases will get a bit large. > > If this does what is needed then LEAST() is also possible. Apart from NULL handling, this matches my current simulation of GREATEST() / LEAST(): https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/Greatest.java I'll have to double-check the NULL case. I think that some databases ignore NULL, here. But I think that your solution is better in general. The problem with this is that the resulting case expression's parse-time complexity is O(n!) with n=number of arguments. For something that can be calculated in O(n) within a loop. Not good :-) > RPAD() and LPAD() look a lot more complicated, but a nested case may allow > a large number of edge cases to be handled in a single logic structure as > the entry values could be checked via the case and the correct string > mapping performed. I give you the solution for that one. It's too geeky :-) ... and I'm open to simpler solutions, of course... http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite/ I really cannot believe that the SQLite database ships with the incredibly useful zeroblob() and randomblob() functions, but not with repeat(), lpad() or rpad() > As for START WITH ... CONNECT BY ... using CTE, I don't think this is > something that can be handled by some geeky SQL, rather jOOQ would end up > doing some form of SQL tranformation. Precisely. There are some known SQL transformation rules for this. There are some links on this Stack Overflow question: http://stackoverflow.com/q/6401222/521799 So far, I haven't had the time to look into this, though. It certainly isn't easy to solve. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
