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.
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.
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.
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.
Roger
On Monday, June 24, 2013 5:27:24 PM UTC+1, Lukas Eder wrote:
>
> Hi Roger,
>
> That was geeky! ;-) If you're looking for more challenges, how would you
> simulate GREATEST() and LEAST()? Or RPAD() and LPAD() in SQLite?
>
> And the killer is: How to simulate START WITH ... CONNECT BY ... using CTE
> (including all functions and pseudo-columns)
>
> But very interesting! Your first solution looks just like that in
> java.lang.Long.bitCount(). I guess that would really be the optimal
> implementation. If unsigned shifting was available. But optimal is tricky
> to define in this case, as some databases do not implement shifting or
> bit_and... So I agree with you that somewhat "readable" might be OK in this
> case.
>
> Cheers and thanks for your insight!
> Lukas
>
>
> 2013/6/24 Roger Thomas <[email protected] <javascript:>>
>
>> I've spent some time looking at other options and have now come up with
>> the following
>>
>> SELECT mod (
>> (
>> (((results.myValue - ((results.myValue >> 1) &
>> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) +
>> ((results.myValue - ((results.myValue >> 1) &
>> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >>
>> 3)) & 8198552921648689607)
>> +
>> ((((results.myValue -((results.myValue >> 1) &
>> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) +
>> ((results.myValue - ((results.myValue >> 1) &
>> 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >>
>> 3)) & 8198552921648689607)
>> ) >> 6
>> )
>> ) & 8088187786164957247
>> )
>> ,4095) AS Result
>> FROM
>> (
>> SELECT 1256789 AS myValue
>> ) AS results
>>
>>
>> With some testing this seems to correctly count bits for 64bit long ints
>> on at least MYSQL (currently my only test target).
>>
>> Its a version of a 1970's solution known as HAKMEM 169, which was
>> orginally coded for the PDP10. Full background can be found on the web and
>> there is some nice detail here
>>
>>
>> http://blogs.msdn.com/b/jeuge/archive/2005/06/08/hakmem-bit-count.aspx
>>
>> A short background is that it works by suming up the number of bits in
>> each block of 3 bits, it then combines the results of each set of 2 blocks
>> and then finally uses MOD to create a sum of all the results. All of the
>> long 'magic' numbers make far more sense if viewed as octal values as they
>> are used as bitwise masks. The reason why the code is so small is because
>> each operation is working on a total of 22 blocks (21 x 3 bit and 1 x 1
>> bit). Its not used nowadays as the MOD function is costly compaired to
>> other coding options.
>>
>> For general ease of use I would say that the solution already included in
>> jOOQ is good enough and far easier for people to understand if they look at
>> the queries. Compaired to my original post, this solution should be better
>> across platforms as it only reduces the 64 bit value so there is no
>> problems with maths overflows.
>>
>>
>> Roger
>>
>> --
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
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.
