2013/6/25 Roger Thomas <[email protected]> > 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. >> > > I considered this, but the comparision rule will be different if char, > varchar vaules are returned. GREATEST() seems to use a simple compaire > while MAX() will use the default collating table. So during my web searchs > I can across a number of comments that MAX() is not truly the same as > GREATEST(). >
Hm, yes. This is subtle. But I'm not sure if all implementations of GREATEST() behave the same way either. I'll have to review and document this. > There is also an edge case problem with my solution - one of the > parameters is a non repeatable function (such as some type of random value) > the collection of CASE statements will cause the function to be called a > number of times. The only way to resolve this would be to move the whole > query into a subselect so all the parameters for GREATEST() are evaulated > only once in the subselect and then processed in the outer SELECT. > That's a very good point in general. It essentially boils down to asking whether function arguments can be considered DETERMINISTIC, in the Oracle sense of the word. This question isn't limited to the simulation of GREATEST(), but to many SQL clause simulations. E.g. NVL(): NVL(a, b) = CASE WHEN a IS NOT NULL THEN a ELSE b END I'll have to review jOOQ implementations: https://github.com/jOOQ/jOOQ/issues/2557 Maybe, this should be generally documented along with the @Support annotation. This could qualify as a formal distinction between "simple" and "transformative" simulations in the sense of this parallel discussion here: https://groups.google.com/forum/#!topic/jooq-user/mLcxnwvglf0 > 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<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. >> > > Your link, takes you to the same place as I found > > > http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/ > > This details a number of solutions that deal with the different solutions > required for all the different versions of START WITH ... CONNECT BY, the > problem being that a number of them need a function defined. For other > cases there does seem to be a possible answer, but the jOOQ paser would > have to recognize the exact SQL statement that can be transformed and from > your past coments I'm not sure you have that form of pattern matching in > place. > I'm not sure if parsing or pattern matching is needed. jOOQ internally maintains an AST (the "model API") with formal types modelling CONNECT BY clauses and functions/pseudo-columns. It's certainly possible to transform them into a somewhat equivalent CTE. But it will be far from easy, as the functions and pseudo-columns can (probably) appear anywhere. I don't know what would happen to something like this: GROUP BY ROLLUP(SYS_CONNECT_BY_PATH(name || LEVEL, '/'), LEVEL) :-) -- 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.
