Inner Memeber of Outer Join Clause ERROR

[rougex]

Hello!
Using Reportsmith I am creating Benefit Statements for our organization. I need an outer join on the 'EE' table when linked to the remaining tables in order to return all employees regardless if they have opted for benefits or not. When trying to add their dependents, I can add the dependent number from one table (a), but their name is on another table (b). I have to create a join between these two and it is causing an inner member error. Please help!



SELECT
EE.EMPLID, EE.NAME, DP_MEDICAL.PLAN_TYPE, DP_MEDICAL.BENEFIT_PLAN, MEDICAL.PLAN_TYPE, MEDICAL.BENEFIT_PLAN, DENTAL.PLAN_TYPE, DENTAL.BENEFIT_PLAN, DP_DENTAL.PLAN_TYPE, DP_DENTAL.BENEFIT_PLAN, LIFE_21.PLAN_TYPE, LIFE_21.BENEFIT_PLAN, LIFE_22.EMPLID, LIFE_22.PLAN_TYPE, LIFE_22.BENEFIT_PLAN, LIFE_23.PLAN_TYPE, LIFE_23.BENEFIT_PLAN, LIFE_24.PLAN_TYPE, LIFE_24.BENEFIT_PLAN, LIFE_25.PLAN_TYPE, LIFE_25.BENEFIT_PLAN, STD.PLAN_TYPE, STD.BENEFIT_PLAN, LTD.PLAN_TYPE, LTD.BENEFIT_PLAN, MED_DEP.PLAN_TYPE, MED_DEP.DEPENDENT_BENEF
FROM
PS_PERSONAL_DATA EE , PS_HEALTH_BENEFIT DP_MEDICAL , PS_JOB JOB , PS_HEALTH_BENEFIT MEDICAL , PS_HEALTH_BENEFIT DENTAL , PS_HEALTH_BENEFIT DP_DENTAL , PS_LIFE_ADD_BEN LIFE_21 , PS_LIFE_ADD_BEN LIFE_22 , PS_LIFE_ADD_BEN LIFE_23 , PS_LIFE_ADD_BEN LIFE_24 , PS_LIFE_ADD_BEN LIFE_25 , PS_DISABILITY_BEN STD , PS_DISABILITY_BEN LTD , PS_HEALTH_DEPENDNT MED_DEP
WHERE
((((JOB.EFFSEQ= (
SELECT MAX(INNERALIAS.EFFSEQ)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = JOB.EMPLID
AND INNERALIAS.EFFDT = JOB.EFFDT)
AND
JOB.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = JOB.EMPLID
AND INNERALIAS.EFFDT <= GetDate()))) AND
(MED_DEP.PLAN_TYPE = '10') AND
(EE.EMPLID*=MED_DEP.EMPLID) AND
(MED_DEP.DEPENDENT_BENEF = '01') AND
(LTD.PLAN_TYPE = '31') AND
(LTD.COVERAGE_ELECT = 'E') AND
(EE.EMPLID*=LTD.EMPLID) AND
(STD.PLAN_TYPE = '30') AND
(STD.COVERAGE_ELECT = 'E') AND
(EE.EMPLID*=STD.EMPLID) AND
(LIFE_25.PLAN_TYPE = '25') AND
(LIFE_25.COVERAGE_ELECT = 'E') AND
(EE.EMPLID*=LIFE_25.EMPLID) AND
(LIFE_24.PLAN_TYPE = '24') AND
(LIFE_24.COVERAGE_ELECT = 'E') AND
(EE.EMPLID*=LIFE_24.EMPLID) AND
(LIFE_23.COVERAGE_ELECT = 'E') AND
(LIFE_23.PLAN_TYPE = '23') AND
(EE.EMPLID*=LIFE_23.EMPLID) AND
(MEDICAL.COVERAGE_ELECT = 'E') AND
((MEDICAL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = MEDICAL.EMPLID
AND INNERALIAS.PLAN_TYPE = MEDICAL.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(JOB.EMPL_STATUS IN( 'A', 'L', 'S')) AND
(MEDICAL.PLAN_TYPE = '10') AND
((DP_MEDICAL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = DP_MEDICAL.EMPLID
AND INNERALIAS.PLAN_TYPE = DP_MEDICAL.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(DP_MEDICAL.PLAN_TYPE = '12') AND
(EE.EMPLID*=DP_MEDICAL.EMPLID) AND
(EE.EMPLID*=MEDICAL.EMPLID) AND
((DENTAL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = DENTAL.EMPLID
AND INNERALIAS.PLAN_TYPE = DENTAL.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(DENTAL.PLAN_TYPE = '11') AND
(EE.EMPLID*=DENTAL.EMPLID) AND
((DP_DENTAL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_BENEFIT INNERALIAS
WHERE INNERALIAS.EMPLID = DP_DENTAL.EMPLID
AND INNERALIAS.PLAN_TYPE = DP_DENTAL.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(DP_DENTAL.PLAN_TYPE = '13') AND
(EE.EMPLID*=DP_DENTAL.EMPLID) AND
((LIFE_21.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_LIFE_ADD_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LIFE_21.EMPLID
AND INNERALIAS.PLAN_TYPE = LIFE_21.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(LIFE_21.PLAN_TYPE = '21') AND
(EE.EMPLID*=LIFE_21.EMPLID) AND
(LIFE_21.COVERAGE_ELECT = 'E') AND
((LIFE_22.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_LIFE_ADD_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LIFE_22.EMPLID
AND INNERALIAS.PLAN_TYPE = LIFE_22.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
(LIFE_22.PLAN_TYPE = '22') AND
(EE.EMPLID*=LIFE_22.EMPLID) AND
(LIFE_22.COVERAGE_ELECT = 'E') AND
((LIFE_23.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_LIFE_ADD_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LIFE_23.EMPLID
AND INNERALIAS.PLAN_TYPE = LIFE_23.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((LIFE_24.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_LIFE_ADD_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LIFE_24.EMPLID
AND INNERALIAS.PLAN_TYPE = LIFE_24.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((LIFE_25.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_LIFE_ADD_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LIFE_25.EMPLID
AND INNERALIAS.PLAN_TYPE = LIFE_25.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((STD.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_DISABILITY_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = STD.EMPLID
AND INNERALIAS.EFFDT <= GetDate()))) AND
((LTD.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_DISABILITY_BEN INNERALIAS
WHERE INNERALIAS.EMPLID = LTD.EMPLID
AND INNERALIAS.EFFDT <= GetDate()))) AND
((MED_DEP.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_HEALTH_DEPENDNT INNERALIAS
WHERE INNERALIAS.EMPLID = MED_DEP.EMPLID
AND INNERALIAS.PLAN_TYPE = MED_DEP.PLAN_TYPE
AND INNERALIAS.EFFDT <= GetDate())))))
AND
(EE.EMPLID = JOB.EMPLID)
GROUP BY
EE.EMPLID, EE.NAME, DP_MEDICAL.PLAN_TYPE, DP_MEDICAL.BENEFIT_PLAN, MEDICAL.PLAN_TYPE, MEDICAL.BENEFIT_PLAN, DENTAL.PLAN_TYPE, DENTAL.BENEFIT_PLAN, DP_DENTAL.PLAN_TYPE, DP_DENTAL.BENEFIT_PLAN, LIFE_21.PLAN_TYPE, LIFE_21.BENEFIT_PLAN, LIFE_22.EMPLID, LIFE_22.PLAN_TYPE, LIFE_22.BENEFIT_PLAN, LIFE_23.PLAN_TYPE, LIFE_23.BENEFIT_PLAN, LIFE_24.PLAN_TYPE, LIFE_24.BENEFIT_PLAN, LIFE_25.PLAN_TYPE, LIFE_25.BENEFIT_PLAN, STD.PLAN_TYPE, STD.BENEFIT_PLAN, LTD.PLAN_TYPE, LTD.BENEFIT_PLAN, MED_DEP.PLAN_TYPE, MED_DEP.DEPENDENT_BENEF
ORDER BY
EE.EMPLID

 

[SQLBill]

It would really help if we knew what the error was.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

LEt's start with putting this into ANSI standard joins. Those oldstyle joins do not work correctly if there is an outer join in SQL Server 2000 and do not work at all in SQL Server 2005. YOu must learn to stop using them.

Questions about posting. See faq183-874