December 6, 2002 at 12:48 pm
Is there a better way to write this?
SELECT
A.SSN
,A.SSN
,B.PAN_ACF2_ID
,G.SSN
,D.PAN_ORGID
,C.DEPTID
,DECODE(C.EMPL_STATUS,'A','A','L','A','P','A','T','T','D','D','S','A')
,A.NAME
,A.PREFERRED_NAME
,B.BUSINESS_TITLE
,E.STREET1
,E.STREET2
,E.CITY
,E.STATE
,E.ZIP
,B.WORK_PHONE
,B.MAIL_DROP
FROM PS_PERSONAL_DATA A,
PS_EMPLOYMENT B,
PS_JOB C,
PS_DEPT_TBL D,
PS_LOCATION_TBL E,
PS_COMPANY_TBL F,
PS_PERSONAL_DATA G
WHERE A.EMPLID = B.EMPLID
AND A.EMPLID = C.EMPLID
AND B.SUPERVISOR_ID = G.EMPLID (+)
AND C.DEPTID = D.DEPTID
AND C.REPORTING_LOCATION = E.LOCATION
AND C.COMPANY = F.COMPANY
AND F.PAN_COBRA_IND = 'N'
AND C.EFFDT = (SELECT MAX(C1.EFFDT)
FROM PS_JOB C1
WHERE C1.EMPLID = C.EMPLID)
AND C.EFFSEQ = (SELECT MAX(C2.EFFSEQ)
FROM PS_JOB C2
WHERE C2.EMPLID = C.EMPLID
AND C2.EFFDT = C.EFFDT)
AND D.EFFDT = (SELECT MAX(D1.EFFDT)
FROM PS_DEPT_TBL D1
WHERE D1.DEPTID = D.DEPTID)
AND E.EFFDT = (SELECT MAX(E1.EFFDT)
FROM PS_LOCATION_TBL E1
WHERE E1.LOCATION = E.LOCATION)
AND F.EFFDT = (SELECT MAX(F1.EFFDT)
FROM PS_COMPANY_TBL F1
WHERE F1.COMPANY = F.COMPANY)
John Zacharkan
John Zacharkan
December 6, 2002 at 1:04 pm
First of all, this is Oracle syntax with the outer join syntax (+), and the decode function also, so that query won't even work in SQL Server. I would rewrite it using ANSI-92 join syntax. Also, the decode function needs to be replaced by similar functionality for SQL Server.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
December 6, 2002 at 1:15 pm
Thank you - it has to stay an Oracle Select. It's part of a DTS package with a bus. link to an Oracle view that I think stinks, but what can I do.
quote:
First of all, this is Oracle syntax with the outer join syntax (+), and the decode function also, so that query won't even work in SQL Server. I would rewrite it using ANSI-92 join syntax. Also, the decode function needs to be replaced by similar functionality for SQL Server.--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
John Zacharkan
John Zacharkan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply