Home » RDBMS Server » Server Administration » SQL Help - translating a select from MS SQL Server
| SQL Help - translating a select from MS SQL Server [message #371758] |
Sat, 02 December 2000 14:59  |
Steve Battisti
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
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 e-mail me at battists@bsci.com.
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
|
|
|
|
| Re: SQL Help - translating a select from MS SQL Server [message #371762 is a reply to message #371758] |
Mon, 04 December 2000 07:51  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
Try this........
SELECT distinct A1.emplid as padr_emplid, b1.name as employee_name,
b2.name as supervisor_name, b3.name as manager_name, decode(a1.supervisor_id ||dirreps, '1005940hrreps', 'SUPER',
a1.manager_id || dirreps,'1005940hrreps', 'SUPERMAN' ,a1.hr_responsible_id, '1005940', 'HRMAN',hrreps, 'hrreps', 'HRMAN') 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
|
|
|
|
| Re: SQL Help - translating a select from MS SQL Server [message #371763 is a reply to message #371758] |
Mon, 04 December 2000 07:51 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
Try this........
SELECT distinct A1.emplid as padr_emplid, b1.name as employee_name,
b2.name as supervisor_name, b3.name as manager_name, decode(a1.supervisor_id ||dirreps, '1005940hrreps', 'SUPER',
a1.manager_id || dirreps,'1005940hrreps', 'SUPERMAN' ,a1.hr_responsible_id, '1005940', 'HRMAN',hrreps, 'hrreps', 'HRMAN') 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
|
|
|
|
Goto Forum:
Current Time: Thu May 02 00:13:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
