Help needed on Nested Decodes

[StevenB]

Howdy folks,

I'm having some trouble with nested decodes. I have a Case statement from MS SQL server that I'm trying to translate into Oracle using nested decodes:

case when (a1.supervisor_id='1005940' and 'dirreps'='hrreps') then 'SUPER' when (a1.manager_id='1005940' and 'dirreps'='hrreps') then 'SUPERMAN' when (a1.hr_responsible_id='1005940' or 'hrreps'='hrreps') then 'HRMAN'

Any ideas as to how to approach this?

Steve Battisti

 

[Advocate]

Steve - why not IF THEN ELSE type of approach ?

'SUPER', 'SUPERMAN' and 'HRMAN' are functions or assignments ???

if (a1.supervisor_id='1005940' and 'dirreps'='hrreps') then SUPER;
elseif (a1.manager_id='1005940' and 'dirreps'='hrreps') then SUPERMAN;
elseif (a1.hr_responsible_id='1005940' or 'hrreps'='hrreps') then HRMAN;
end if;
Best of Irish Luck, David.
djwilkes@hotmail.com

 

[MikeJones]

create or replace Function Whatever (supervisor_id number, manager_id number, etc.... )

return varchar2

IS
begin
if (a1.supervisor_id='1005940' and 'dirreps'='hrreps') then
return 'SUPER';
elsif (a1.manager_id='1005940' and 'dirreps'='hrreps') then
return 'SUPERMAN';
elsif (a1.hr_responsible_id='1005940' or 'hrreps'='hrreps') then
return 'HRMAN';
end if;
end;

ta da!!

 

[CRoberts]

Have made a mistake? I am not very familiar with MS SQL Server, but you appear to have several strings evaluations such as 'dirreps'='hrreps' and 'hrreps'='hrreps'. The way it seems to read is that the last condition "OR 'hrreps' = 'hrreps'" is always true. The function as written in PL/SQL for sure will always return a 'HRMAN'. If checking for some column with a value of 'hrreps', then a you could do:

DECODE(A2.Some_Column, 'hrreps',
DECODE(A1.Supervisor_Id, '1005940', 'SUPER',
DECODE(A1.Manager_Id, '1005940', 'SUPERMAN',
DECODE(A1.Hr_REsponsible_Id, '1005940', 'HRMAN', NULL)))) AS Some_Title