troubleshooting Question

Left out join from two tables fails

Avatar of hmstechsupport
hmstechsupport asked on
Microsoft SQL ServerOracle Database
6 Comments1 Solution458 ViewsLast Modified:
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;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros