Re: SQL Help - Case Statements

I'm with Helen on this one. I've never heard of an embedded "CASE" statement in a SQL query (though I'm always ready to learn). The DECODE should work, or doing this in a PL/SQL stored procedure returning the result in a refcursor would also work - assuming that your client software knows what to do with a refcursor.

Helen Jian wrote:

> Hi,
>
> I am not sure myself, maybe you can try change
>
> > "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"
>
> to:
>
> decode(a1.supervisor_id || dirreps, '1005940hrreps', 'SUPER',
> decode(a1.manager_id || dirreps, '1005940hrreps', 'SUPERMAN',
> decode(a1.hr_responsible_id || dirreps, '1005940hrreps', 'HRMAN')))
>
> Hope this help
>
> <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 Sat Dec 02 2000 - 17:57:56 CST