Convert code from Implicit to Explicit
-
Hello, All:
This is my first post here, but I'm quite sure you can help.
For years we have been creating and running SQL code using "implicit join notation". The SQL developers here have been around a while and this is what they were comfortable with. Since this was the way I originally learned, also, I've perpetuated the same bad habits.
BUT! The time has come for upgrades to a couple of our major systems. After some quick research, it seems that all or most of our implicit join code containing inner and outer joins is probably going to blow up when we start to test and run it in our new environment.
I am starting to retrain myself, but I'll admit it's not easy changing the way you've thought for 7 years. For example, we have a piece of SQL code where some variation is used in almost every query we run. It is as follows:
SELECT
JOB.EMPLID,
JOB.FILE_NBR,
PER.NAME,
JOB.PAYGROUP,
JOB.DEPTID,
JOB.HOME_DEPARTMENT,
JOB.EMPL_STATUS
FROM PS_JOB JOB, PS_PERSONAL_DATA PER
WHERE JOB.EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A
WHERE A.EMPLID = JOB.EMPLID
AND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND A.EFFDT <= SYSDATE)
AND JOB.EFFSEQ = (SELECT MAX(A.EFFSEQ) FROM PS_JOB A
WHERE A.EMPLID = JOB.EMPLID
AND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND A.EFFDT = JOB.EFFDT)
AND JOB.EMPL_STATUS IN ('A','L','P','S')
AND JOB.EMPLID = PER.EMPLID
With the two sub-queries referencing one of the main tables, I can't for the life of me seem to figure out how to convert the above into the Explicit syntax. Can someone give me a hand here?
Much appreciated.
Thom
-
here's my best guess; I had trouble identifying what is supposed to be the A alias, especially since it seems to appear twice.
SELECTJOB.EMPLID,JOB.FILE_NBR,PER.NAME,JOB.PAYGROUP,JOB.DEPTID,JOB.HOME_DEPARTMENT,JOB.EMPL_STATUSFROM PS_JOB JOBINNER JOIN PS_PERSONAL_DATA PERON JOB.EMPLID = PER.EMPLIDINNER JOIN (SELECT EMPLID,MAX(A.EFFDT) AS EFFDT FROM PS_JOB GROUP BY EMPLID) AON JOB.EMPLID = A.EMPLIDWHERE JOB.EFFDT = A.EFFDTAND JOB.EMPL_RCD_NBR = A.EMPL_RCD_NBRAND A.EFFDT <= GETDATE())AND JOB.EFFSEQ = A.EFFDTAND JOB.EMPL_RCD_NBR = A.EMPL_RCD_NBRAND JOB.EMPL_STATUS IN ('A','L','P','S')Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here's another guess
SELECT JOB.EMPLID,JOB.FILE_NBR,PER.NAME,JOB.PAYGROUP,JOB.DEPTID,JOB.HOME_DEPARTMENT,JOB.EMPL_STATUSFROM PS_JOB JOBINNER JOIN PS_PERSONAL_DATA PER ON JOB.EMPLID = PER.EMPLIDWHERE JOB.EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB AWHERE A.EMPLID = JOB.EMPLIDAND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBRAND A.EFFDT <= SYSDATE)AND JOB.EFFSEQ = (SELECT MAX(A.EFFSEQ) FROM PS_JOB AWHERE A.EMPLID = JOB.EMPLIDAND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBRAND A.EFFDT = JOB.EFFDT)AND JOB.EMPL_STATUS IN ('A','L','P','S');____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Sorry. I should have explained this portion of the statement:
WHERE JOB.EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A
WHERE A.EMPLID = JOB.EMPLID
AND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND A.EFFDT <= SYSDATE)
AND JOB.EFFSEQ = (SELECT MAX(A.EFFSEQ) FROM PS_JOB A
WHERE A.EMPLID = JOB.EMPLID
AND A.EMPL_RCD_NBR = JOB.EMPL_RCD_NBR
AND A.EFFDT = JOB.EFFDT)
The PS_JOB table has multiple rows for each employee and the piece above isolates and pulls out the current active row without grabbing a future row.
-
Mark-101232, was going to go down that road, but I thought I needed to add an INNER JOIN statement to the two sub queries. If this not the case, I suppose I'm all set.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply