alternative for: Outerjoin & OR / IN 2

[sailsail]

I have a situation similar to the following: two TABLES:
Section and Supplemental_CourseData

section (sec_semester,sec_crn, sec_subjcode, sec_course_num, sec_shcd_code)
Primary key: (sec_semester,sec_crn)

Supplemental_CourseData( sup_subjcode, sup_course_num, sUP_eff_semester, sup_cuda_code)
Primary key: (sup_subjcode, sup_course_num, sUP_eff_semester,)
sup_cuda_code can have values: COUR_EXEMPT, NULL, NON_REV)

I need to pull all records from SETION table where 1) sec_shcd_code is A, or B or C.

2)When sec_shcd_code is 'F' and if the record not found in SUP table I need to include this. (This is the reason I am using 'OUTER JOIN').
3)When 'F' and if the record is found in SUP table then check for 'sup_cuda_code = COUR_EXEMPT' and exclude those corresponding
records from SECTION table.

SELECT .....from SECTION, Supplemental_CourseData
WHERE ........
AND (sec_shcd_code IN ('A', 'B', 'C')
OR ((sec_shcd_code ='F'
AND (sup_cuda_code <> 'COUR_EXEMPT'
OR sup_cuda_code IS NULL
)
)
AND sup_subjcode(+) = sec_subjcode
AND sup_course_num(+) = sec_course_num
AND sUP_eff_semester(+) =
(SELECT MAX (sUP_eff_semester)
FROM Supplemental_CourseData
WHERE sUP_eff_semester <= sec_semester
AND sup_subjcode = sec_subjcode
AND sup_course_num = sec_course_num)
)
)
Outer join does not accept sub query, so I modified the query by calling a function(f_get_sUP_eff_semester) as:

SELECT .....from SECTION, Supplemental_CourseData
WHERE ........
AND (sec_shcd_code IN ('A', 'B', 'C')
OR ((sec_shcd_code= 'F'
AND (sup_cuda_code <> 'COUR_EXEMPT'
OR sup_cuda_code IS NULL
)
)
AND sup_subjcode(+) = sec_subjcode
AND sup_course_num(+) = sec_course_num
AND sUP_eff_semester(+) =
f_get_sUP_eff_semester(sec_semester,sec_subjcode,sec_course_num)
)

still having problem with 'OR' in 4th line i.e.'(OR ((sec_shcd_code= 'F')'. I cannot use 'OR' with outer join. If I remove 'OR' outer join works fine in this case.
My business requirment needs 'outerjoin' & 'OR'. Is there any other way I can write this query. Thanks a lot.

 

[taupirho]

Considered UNIONS?


In order to understand recursion, you must first understand recursion.

 

[jimirvine]

Either that or look at ANSI SQL join syntax which WILL allow you to use a subquery in the join

 

[sailsail]

jimirvine ,

Thanks for the the suggestion. I used ANSI SQL and the problem is solved.