I have the following Oracle code to convert to SQL but I am stuck, please help.
I know decode is like a case in SQL and greatest is like max ( not sure) but not sure how to convert this to SQL
Thanks!
Blockquote
(select decode(greatest(count(),2),count(),'Y','') ---Oracle
from EMPL_CURR_h2
where h2.supervisor_id = ECV.EMPLID
and h2.empl_status in('A','L','S','P')
and h2.empl_type = 'H') AS Supervise_2_hourly,
Seems a very convoluted way or writing that though - assuming I've correctly understood what the creator intended?!!
CASE WHEN
(
SELECT COUNT(*) AS h2_Count
from DM_EMPL_CURR_VW h2
where h2.supervisor_id = ECV.EMPLID
and h2.empl_status in('A','L','S','P')
and h2.empl_type = 'H'
) >= 2 THEN 'Y' ELSE '' END
Thank you Kristen, yes these codes are a bit old and who ever wrote them was using old schooling I guess and I am trying to convert them from Oracle to SQL.
Thanks bitsmed how do I insert this into sql statement ? or whats the sql equivalent?
CASE WHEN
(
SELECT COUNT(*) AS h2_Count
from DM_EMPL_CURR_VW h2
where h2.supervisor_id = ECV.EMPLID
and h2.empl_status in('A','L','S','P')
and h2.empl_type = 'H'
) >= 2 THEN 'Y' ELSE '' END
Modified @Kristen version (if my oracle is not too rusty)
CASE WHEN
(
SELECT COUNT(*) AS h2_Count
from DM_EMPL_CURR_VW h2
where h2.supervisor_id = ECV.EMPLID
and h2.empl_status in('A','L','S','P')
and h2.empl_type = 'H'
) = 2 THEN 'Y' ELSE '' END
I'm rusty too, but I thought DECODE matched first two parameters and if equal returns the next parameter as the result, the final parameter is the ELSE result.
So if COUNT(*) <= 1 then GREATEST will return 2 (the second, larger, parameter). If COUNT(*) >=2 then GREATEST will return that value
The next parameter to DECODE is COUNT(*), so if COUNT(*) is >=2 that will be returned by GREATEST and then DECODE will match that with the COUNT(*) parameter, and then return 'Y'.
I expect even an Oracle programmer would have to scratch their head to "read" this, its dreadful, and I'm sure there are much better alternative ways of writing it in Oracle.
Although, that said, the whole concept of DECODE is dreadful - very cryptic to read, compared to something like the clarity of a CASE statement.
If count is 1 then greatest is 2. The decode then compares that result with count (which is 1) and if they are equal, then 'Y' else '' - here the reault is ''
If count is 2 then greatest is 2. The decode then compares that result with count (which is 2) and if they are equal, then 'Y' else '' - here the result is 'Y'
If count is 3 then greatest is 3. The decode then compares that result with count (which is 3) and if they are equal, then 'Y' else '' - here the result is ''
I would be concerned (in MS SQL) about performance of a scalar function like that, particularly its impact on SARGablility of queries when used in WHERE clause or JOIN.