Avatar of hmstechsupport
hmstechsupport asked on

Left out join from two tables fails

I am trying to LEFT JOIN the table called CHRRES which has two keys in it from 2 other tables, but I cannot find the right way to order the JOIN and FROM clauses.
EMPHIS.EMP_RES joins to CHRRES.CRS_RES and CHRHIS.CHH_CHR joins to CHRRES.CRS_CHR.
It must be a LEFT join as there is some data that exists in the main select that does not have joining data in the CHRRES table and that is fine, but I need all the rows returned regardless.

Although the SQL below is specific to Oracle it also fails in SQL Server (example 3) with a similar error.

How can I write this so that it will work everytime?

Doing it like this gives me an invalid identifier for EMH_RES. If I reverse the FROM clause to have CHRHIS followed by EMPHIS then the invalid identifier becomes the CHH_CHR column.

  SELECT PSD_KEY,PSH_PEDATE,To_Char(To_Date(PSH_PEDATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_PEDATE,PSD_DATE,
      To_Char(To_Date(PSD_DATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_DATE,
      EMH_CODE,PSL_PAPROJ,PSL_PRJ,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0 THEN PSD_MIN/60 END AS PSD_HRS,
      SUBSTR(A.TCN_NOTE,1,240) AS PSL_NOTES,EMH_FLD6,RAT_FLD1,PSL_FLD4,PSL_FLD5,
      PSL_FLD1,SubStr(PSL_FLD2,INSTR(PSL_FLD2, '.', 1, 1)+1) AS PSL_FLD2,SubStr(PSL_FLD3,INSTR(PSL_FLD3, '.', 1, 2)+1) AS PSL_FLD3,SubStr(B.TCN_NOTE,1,200) AS PSH_NOTES
      FROM PSDETAIL,EMPHIS,CHRHIS LEFT JOIN CHRRES ON (CRS_CHR=CHH_CHR AND CRS_RES=EMH_RES),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND (PSD_PADATA IS NULL OR PSD_PADATA=' ') AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY
 
I tried this thinking this might work by separating the LEFT joins and then tying them together in the WHERE clause, and although I get results I am clearly missing rows so this is not working correctly.

  SELECT PSD_KEY,PSH_PEDATE,To_Char(To_Date(PSH_PEDATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_PEDATE,PSD_DATE,
      To_Char(To_Date(PSD_DATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_DATE,
      EMH_CODE,PSL_PAPROJ,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0 THEN PSD_MIN/60 END AS PSD_HRS,
      SUBSTR(A.TCN_NOTE,1,240) AS PSL_NOTES,EMH_FLD6,RAT_FLD1,PSL_FLD4,PSL_FLD5,
      PSL_FLD1,SubStr(PSL_FLD2,INSTR(PSL_FLD2, '.', 1, 1)+1) AS PSL_FLD2,SubStr(PSL_FLD3,INSTR(PSL_FLD3, '.', 1, 2)+1) AS PSL_FLD3,SubStr(B.TCN_NOTE,1,200) AS PSH_NOTES,
  B.CRS_RACCT
      FROM PSDETAIL,EMPHIS LEFT JOIN CHRRES A ON (A.CRS_RES=EMPHIS.EMH_RES),CHRHIS LEFT OUTER JOIN CHRRES B ON (CRS_CHR=CHRHIS.CHH_CHR),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND (PSD_PADATA IS NULL OR PSD_PADATA=' ') AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY AND A.CRS_KEY=B.CRS_KEY

Here is an example that fails in SQL Server with "The multi-part identifier "EMPHIS.EMH_RES" could not be bound."

SELECT PSD_KEY,PSH_PEDATE,PSH_PEDATE,PSD_DATE,
      EMH_CODE,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0
    THEN PSD_MIN/60 END AS PSD_HRS,
      EMH_FLD6,RAT_FLD1,
      PSL_FLD1,PSL_FLD2,PSL_FLD3,B.TCN_NOTE AS PSH_NOTES
      FROM PSDETAIL,EMPHIS,CHRHIS LEFT JOIN CHRRES ON (CRS_CHR=CHRHIS.CHH_CHR AND CRS_RES=EMPHIS.EMH_RES),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY;
Microsoft SQL ServerOracle Database

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Brian Crowe

Your syntax is off in the SQL Server example.  What are the commas doing in the JOIN clauses?

FROM PSDETAIL,EMPHIS,CHRHIS
LEFT JOIN CHRRES
   ON (CRS_CHR=CHRHIS.CHH_CHR AND CRS_RES=EMPHIS.EMH_RES),
  PSHEADER

LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
Brian Crowe

I just noticed the joining clauses at the end.  There are no table qualifiers so I'm making an assumption.
Anthony Perkins

It would really help your cause if you include the aliases for all the columns.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
hmstechsupport

Wow.  

This is exactly what I need!  I guess my lesson learned for today is the "you really should NOT mix ANSI joins and old fashioned joining (via the where clause) going 100% ANSI syntax will definitely help"

This was extremely helpful.  I have yet to compare the contents columns by column, but the rows returned are the exact number I expected so that's a really good sign.
Thank you for your advice and the actual SQL which fired off beautifully as is so you guessed correctly.
PortletPaul

:) good guess then, cheers, Paul