1

I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns:

TERM_CODE, STU_INST_UID, MAJOR_DESC, MAJOR_CIP, B_GRADUATION_TERM

keyed on prog_enroll.STU_INST_UID = grad.STU_INST_UID and prog.enroll TERM_CODE = B_GRADUATION_TERM and include all rows from both tables. I am, however, missing rows from T_ADMGRAD_STUDENT where the value B_GRADUATION_TERM is not in the TERM_CODE. What am I doing wrong?

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;
  • Post the database version and execution plan of the query. There are some wrong result bugs related to FULL OUTER JOIN. – Balazs Papp Feb 7 '16 at 16:49
1

Perhaps changing your JOIN condition slightly might help if you want the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join :

From:

grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

To:

 (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);

As shown completely here:

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
(grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.