Help with Oracle to Sql

Hi ,

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,

Blockquote

Looks like if COUNT(*) >=2 then 'Y' else ''

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.

What about the "Greatest" function?
Pasi.

I'm a bit rusty in oracle, but I belive logic should be
if (count(*)=2 then 'Y' else ''

greatest function return highest value ex.
greatest(1,2) returns 2
greatest(3,2) returns 3

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

Thanks but it looks the same what a bout the "Greatest" where do I inset this in query?
Pasi

I changed >= to =

What @Kristen showed you, has same logic as the oracle query and is much more readable - your oracle statement

decode(greatest(count(*),2),count(),'Y','')

is confusing (to say the least). See the statement already confused either @Kristen or me :smiley:

If you encounter other greatest functions, a case statement can be used. Example

greatest(count(*),2)

can be written as

case when count(*)>2 then count(*) else 2 end

Thanks so much, You guys rock!

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.

P.S. I presume the Oracle count() functions the same as COUNT(*)?

Lets evaluate

decode(greatest(count(*),2),count(*),'Y','')

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 ''

At least, that how I understand the decode.

I agree, but you changed my code from

) >= 2 THEN 'Y' ELSE '' END

to

) = 2 THEN 'Y' ELSE '' END

which I don't think is right. What say you?

Hmm, I hate that I can't illustrate with colors :frowning:

If count is 1

  • then greatest(count(*),2) evaluates to 2
  • decode(2,count(*),'Y','') evaluates to '' as 2 != 1

If count is 2

  • then greatest(count(*),2) evaluates to 2
  • decode(2,count(*),'Y','') evaluates to 'Y' as 2 = 2

If count is 3

  • then greatest(count(*),2) evaluates to 3
  • decode(3,count(*),'Y','') evaluates to 'Y' as 3 != 2

Thus I conclude that ONLY value of 2 results in 'Y', which is why I changed your >= to =

decode(3,count(*),'Y','') evaluates to 'Y'

Agreed, but because 3 = 3 (rather than 3 != 2)

For all greater count values it resolves to COUNT(*) = COUNT(*)

That's my reading of it

1 Like

You correct!

1 Like

Thanks you guys very informative and good lessons!
Pasi.

You might consider creating a dbo.Greatest function.

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.