SQL Help - Case Statements

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"

If you're really turned on by this and have some ideas, you can even call me at 508-650-8518. I'd be happy to call back, even long distance.

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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 02 2000 - 14:43:18 CST