>
> 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(). 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.
> 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.
>
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.
--
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.
