SQL Help! Case statements in Oracle

[StevenB]

Howdy folks,

I have an urgent need for some help modifying a SQL Server statement
into a format that Oracle will like. Here is the statement:

SELECT distinct A1.emplid as padr_emplid, b1.name as employee_name,
b2.name as supervisor_name, b3.name as manager_name, 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' end
as padr_role from PS_BSC_EMP_SUPER as A1, ps_personal_data as B1,
ps_personal_data as B2, ps_personal_data as B3 where
((a1.supervisor_id='1005940' and 'dirreps'='hrreps') or
(a1.manager_id='1005940' and 'dirreps'='hrreps') or
(a1.hr_responsible_id='1005940' and 'hrreps'='hrreps')) and
(a1.emplid=b1.emplid) and (a1.supervisor_id=b2.emplid) and
(a1.manager_id=b3.emplid) order by b1.name

The error we get when we try run this statement in Oracle is:
"ERROR at line 1:
ORA-00923: FROM keyword not found where expected"

We believe the error is coming from this piece of the statement:
"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' end"

We've heard from one Oracle expert that it can't be done in Oracle, but I'm not giving up hope yet!

Thanks,

Steve Battisti

 

[MikeJones]

I Think you're Gonna need to use the DECODE statement, which basically allows you to do If/Case statements in SQL. The statement works like this.

DECODE(Column, 'If it's This Value', 'Then use this value',
'or if this', 'then use this',
'otherwise use this')

So its basicaly a 'if, elsif, elseif.... else' statement.

You can also nest decodes so you can do....

DECODE(Table.column1, 'A',DECODE(table2.column1,'A','B',
C,D),
'Something','Other',
etc., etc.)

You can use literals or table.column references as needed, DECODE Doesn't care and you can nest decode until the cows come home so it's dead fancy.

Finally if this won't work consider moving all the workings into a PL/SQL function and then call this in the SQL. I think a decode will do it though and it will perform a hell of a lot better than a function.

 

[StevenB]

Thanks!

I think we've ended up using decode. It seems to have met our needs.

Thanks for your suggestion!