ReportSmith and SQL Union and Enterprise V4

[hoxrider]

Can the SQL Union function be used within the ReportSmith SQL window? I am not having any luck trying to get my two separate SQL queries to work when I union them. Note - each query works separately.

Any examples of the SQL Union that work within ReportSmith would be appreciated.

 

[hoxrider]

Solved my own problem. I forgot to remove ORDER BY clause from each SQL statement and move it to the end. The UNION works when the ORDER BY is at the end.

SELECT "PER"."SSN", RPAD("MED"."PLAN_TYPE",6,' ') AS TYPE, RPAD("MED"."BENEFIT_PLAN",6,' ') AS PLAN, RPAD("MED"."COVRG_CD",2,' ')||'0000000000'||' '||RPAD(NVL(TO_CHAR("MED"."EFFDT",'MMDDYYYY'),' ' ),8,' ') AS COVRG_LVL
FROM "PS_PERSONAL_DATA" "PER", "PS_HEALTH_BENEFIT" "MED"
WHERE "MED"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_HEALTH_BENEFIT" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "MED"."EMPLID"
AND "INNERALIAS"."COBRA_DEPENDENT" = "MED"."COBRA_DEPENDENT"
AND "INNERALIAS"."EMPL_RCD_NBR" = "MED"."EMPL_RCD_NBR"
AND "INNERALIAS"."PLAN_TYPE" = "MED"."PLAN_TYPE"
AND "INNERALIAS"."BENEFIT_NBR" = "MED"."BENEFIT_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
AND "MED"."PLAN_TYPE" = '10'
AND "MED"."COVERAGE_ELECT" = 'E'
AND ("MED"."DEDUCTION_END_DT" IS NULL
OR "MED"."DEDUCTION_END_DT" > SYSDATE)
AND "PER"."EMPLID" = "MED"."EMPLID"
UNION
SELECT "PER"."SSN", RPAD("DEN"."PLAN_TYPE",6,' ') AS TYPE, RPAD("DEN"."BENEFIT_PLAN",6,' ') AS PLAN, RPAD("DEN"."COVRG_CD",2,' ')||'0000000000'||' '||RPAD(NVL(TO_CHAR("DEN"."EFFDT",'MMDDYYYY'),' ' ),8,' ') AS COVRG_LVL
FROM "PS_PERSONAL_DATA" "PER", "PS_HEALTH_BENEFIT" "DEN"
WHERE "DEN"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_HEALTH_BENEFIT" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "DEN"."EMPLID"
AND "INNERALIAS"."COBRA_DEPENDENT" = "DEN"."COBRA_DEPENDENT"
AND "INNERALIAS"."EMPL_RCD_NBR" = "DEN"."EMPL_RCD_NBR"
AND "INNERALIAS"."PLAN_TYPE" = "DEN"."PLAN_TYPE"
AND "INNERALIAS"."BENEFIT_NBR" = "DEN"."BENEFIT_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
AND "DEN"."PLAN_TYPE" = '11'
AND "DEN"."COVERAGE_ELECT" = 'E'
AND ("DEN"."DEDUCTION_END_DT" IS NULL
OR "DEN"."DEDUCTION_END_DT" > SYSDATE)
AND "PER"."EMPLID" = "DEN"."EMPLID"
UNION
SELECT "PER"."SSN", RPAD("VIS"."PLAN_TYPE",6,' ') AS TYPE, RPAD("VIS"."BENEFIT_PLAN",6,' ') AS PLAN, RPAD("VIS"."COVRG_CD",2,' ')||'0000000000'||' '||RPAD(NVL(TO_CHAR("VIS"."EFFDT",'MMDDYYYY'),' ' ),8,' ') AS COVRG_LVL
FROM "PS_PERSONAL_DATA" "PER", "PS_HEALTH_BENEFIT" "VIS"
WHERE "VIS"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_HEALTH_BENEFIT" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "VIS"."EMPLID"
AND "INNERALIAS"."COBRA_DEPENDENT" = "VIS"."COBRA_DEPENDENT"
AND "INNERALIAS"."EMPL_RCD_NBR" = "VIS"."EMPL_RCD_NBR"
AND "INNERALIAS"."PLAN_TYPE" = "VIS"."PLAN_TYPE"
AND "INNERALIAS"."BENEFIT_NBR" = "VIS"."BENEFIT_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
AND "VIS"."PLAN_TYPE" = '14'
AND "VIS"."COVERAGE_ELECT" = 'E'
AND ("VIS"."DEDUCTION_END_DT" IS NULL
OR "VIS"."DEDUCTION_END_DT" > SYSDATE)
AND "PER"."EMPLID" = "VIS"."EMPLID"
UNION
SELECT "PER"."SSN", RPAD("BAS"."PLAN_TYPE",6,' ') AS TYPE, RPAD("BAS"."BENEFIT_PLAN",6,' ') AS PLAN, ' 0000000000'||' '||RPAD(NVL(TO_CHAR("BAS"."EFFDT",'MMDDYYYY'),' ' ),8,' ') AS COVRG_LVL
FROM "PS_PERSONAL_DATA" "PER", "PS_LIFE_ADD_BEN" "BAS"
WHERE "BAS"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_LIFE_ADD_BEN" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "BAS"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "BAS"."EMPL_RCD_NBR"
AND "INNERALIAS"."PLAN_TYPE" = "BAS"."PLAN_TYPE"
AND "INNERALIAS"."BENEFIT_NBR" = "BAS"."BENEFIT_NBR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)
AND "BAS"."PLAN_TYPE" = '20'
AND "BAS"."COVERAGE_ELECT" = 'E'
AND ("BAS"."DEDUCTION_END_DT" IS NULL
OR "BAS"."DEDUCTION_END_DT" > SYSDATE)
AND "PER"."EMPLID" = "BAS"."EMPLID"
ORDER BY 1,2