troubleshooting Question

How to change *= to LEFT OUTER JOIN for complex SQL statement

Avatar of hmstechsupport
hmstechsupport asked on
Microsoft SQL ServerOracle DatabaseMySQL Server
8 Comments3 Solutions777 ViewsLast Modified:
Hello All!

I'll try to be clear as to what we are doing. First things first we are targeting the following databases:

SQL Server
MySQL
Oracle

So our statement needs to work in or have equivalents for all 3 of these database types. We currently have a working statement for SQL Server but it is very specific to SQL Server because it uses the *= for a left outer join:

SELECT PRJ_KEY, PRJ_NAME, PRJ_DESC, CHH_KEY, CHH_CODE, CHH_DESC, ACT_KEY, ACT_DESC, ACT_ETC, ACT_COMP, 
EMH_CODE, EMH_NAME, RES_KEY, RES_CODE, RES_DESC, CRS_KEY, CRS_ETC, CRS_ETCUPD, CRS_COMP, PSL_RAT_CD, PSL_RATDS, PSD_DATE, SUM(PSD_MIN) AS ACTUALS,
SUM(PSL_RATE1*(PSD_MIN)/60) AS PSD_COST1, SUM(PSL_RATE2*(PSD_MIN/60)) AS PSD_COST2, 
SUM(PSL_RATE3*(PSD_MIN)/60) AS PSD_COST3, SUM(PSL_RATE4*(PSD_MIN)/60) AS PSD_COST4, SUM(PSL_RATE5*(PSD_MIN)/60) AS PSD_COST5, 
SUM(PSL_RATE6*(PSD_MIN)/60) AS PSD_COST6, SUM(PSL_RATE7*(PSD_MIN)/60) AS PSD_COST7, SUM(PSL_RATE8*(PSD_MIN)/60) AS PSD_COST8, 
SUM(PSL_RATE9*(PSD_MIN)/60) AS PSD_COST9, PSD_PMVAPP, PSD_PMVRSRC, PSD_PMVTSTMP 
FROM PSHEADER, EMPHIS, TCRES, PSLINES, PSDETAIL, TCPROJ, CHRHIS, CHRRES, ACTIVITY
WHERE (PSD_MIN <> 0) AND EMH_KEY=PSH_EMH AND EMH_RES *= RES_KEY AND PSL_PSH=PSH_KEY AND PSD_PSL=PSL_KEY AND PRJ_KEY=PSL_PRJ AND CHH_KEY=PSL_CHH AND
(CHH_CHR *= CRS_CHR AND CRS_RES = RES_KEY) AND CHH_KEY *= ACT_CHH
GROUP BY PRJ_KEY, PRJ_NAME, PRJ_DESC, CHH_KEY, CHH_CODE, CHH_DESC, ACT_KEY, ACT_DESC, ACT_ETC, ACT_COMP, 
EMH_CODE, EMH_NAME, RES_KEY, RES_CODE, RES_DESC, CRS_KEY, CRS_ETC, CRS_ETCUPD, CRS_COMP, 
PSL_RAT_CD, PSL_RATDS, PSD_DATE, PSD_PMVAPP, PSD_PMVRSRC, PSD_PMVTSTMP 
ORDER BY CHH_CODE, CHH_KEY

This works the way we need it to in SQL Server. To make it compatible with Oracle and MySQL we tried to generalize the statement but it doesn't work because one of the joins requires 2 conditions:

SELECT PRJ_KEY, PRJ_NAME, PRJ_DESC, CHH_KEY, CHH_CODE, CHH_DESC, ACT_KEY, ACT_DESC, ACT_ETC, ACT_COMP, 
EMH_CODE, EMH_NAME, RES_KEY, RES_CODE, RES_DESC, CRS_KEY, CRS_ETC, CRS_ETCUPD, CRS_COMP, PSL_RAT_CD, PSL_RATDS, PSD_DATE, SUM(PSD_MIN) AS ACTUALS,
SUM(PSL_RATE1*(PSD_MIN)/60) AS PSD_COST1, SUM(PSL_RATE2*(PSD_MIN/60)) AS PSD_COST2, 
SUM(PSL_RATE3*(PSD_MIN)/60) AS PSD_COST3, SUM(PSL_RATE4*(PSD_MIN)/60) AS PSD_COST4, SUM(PSL_RATE5*(PSD_MIN)/60) AS PSD_COST5, 
SUM(PSL_RATE6*(PSD_MIN)/60) AS PSD_COST6, SUM(PSL_RATE7*(PSD_MIN)/60) AS PSD_COST7, SUM(PSL_RATE8*(PSD_MIN)/60) AS PSD_COST8, 
SUM(PSL_RATE9*(PSD_MIN)/60) AS PSD_COST9, PSD_PMVAPP, PSD_PMVRSRC, PSD_PMVTSTMP 
FROM PSHEADER, PSLINES, PSDETAIL, TCPROJ, 
CHRHIS LEFT JOIN ACTIVITY ON ACT_CHH=CHH_KEY,
EMPHIS LEFT JOIN TCRES ON RES_KEY=EMH_RES LEFT OUTER JOIN CHRRES ON CRS_RES=RES_KEY AND CRS_CHR=CHH_CHR
WHERE (PSD_MIN <> 0) AND EMH_KEY=PSH_EMH AND PSL_PSH=PSH_KEY AND PSD_PSL=PSL_KEY AND PRJ_KEY=PSL_PRJ AND CHH_KEY=PSL_CHH
GROUP BY PRJ_KEY, PRJ_NAME, PRJ_DESC, CHH_KEY, CHH_CODE, CHH_DESC, ACT_KEY, ACT_DESC, ACT_ETC, ACT_COMP, 
EMH_CODE, EMH_NAME, RES_KEY, RES_CODE, RES_DESC, CRS_KEY, CRS_ETC, CRS_ETCUPD, CRS_COMP, PSL_RAT_CD, PSL_RATDS, PSD_DATE, PSD_PMVAPP, PSD_PMVRSRC, PSD_PMVTSTMP 
ORDER BY CHH_CODE, CHH_KEY

This should give a better idea of our joins. Notice in the generic SQL we have the LEFT OUTER JOIN on the CHRRES table that we need to join based on 2 conditions. It works quite well in our first statement (that uses *=).

We would like to stay away from needing to manage separate statements (one for each database type) which is why we are trying to find a generic solution for all 3 but we can separate if needed. So the first goal is to hopefully get the generic SQL to work but if that is not possible the goal is to find an equivalent to the working statement for all 3 databases.

If I haven't made this clear enough please ask questions.

Thanks in advance!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 8 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 3 Answers and 8 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