Link to home
Create AccountLog in
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;
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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'),
I just noticed the joining clauses at the end.  There are no table qualifiers so I'm making an assumption.
It would really help your cause if you include the aliases for all the columns.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of hmstechsupport
hmstechsupport

ASKER

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.
:) good guess then, cheers, Paul