Re: SQL Help - Case Statements

Oracle 8.1.6 and beyond have a case statement and Oracle 8.1.5 has been desupported.
Also, IMO, *hardcoded* constructs like this should be avoided where possibly, they should be put in a table, to maintain transparency.

Regards,

Sybrand Bakker, Oracle DBA

<battists_at_bsci.com> wrote in message news:90bmt6$nbi$1_at_nnrp1.deja.com...
> 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 Sun Dec 03 2000 - 01:01:51 CST