I have a table with class_nbr and relate_class_nbr_1, and relate_class_nbr_2.

If you know PeopleSoft it is the ENRL_REQ_DETAIL table.

class_nbr is the main class with CRE as the SSR_COMPONENT. CRE is Credit

relate_class_nbr_1 is a related class usually a LAB or REC.

LAB = Lab
REC = Recitation

How can I select those relate_class_nbr_1 or relate_class_nbr_2 where <> 0 in a single query?

There is either a 0 or there is a class_nbr in the relate_class_nbr_1 or relate_class_nbr_2 field.

I may have instances where the class has a LAB and a REC. Both the LAB and REC are different classes the student enrolled, dropped or swapped. I need to account for these enrolled, dropped, or swapped classes and want to do so in a single query. I don't think this can be done since the first WHEN statement will always be true.


Code:
SELECT PS.OPRID
, RQ.emplid
,CASE
WHEN a.relate_class_nbr_1 <> 0 AND a.relate_class_nbr_2 = 0 THEN
(SELECT a.relate_class_nbr_1
FROM PS_ENRL_REQ_DETAIL a, PS_CLASS_TBL b
WHERE (b.SSR_COMPONENT = 'LAB' OR b.SSR_COMPONENT = 'REC')
AND a.relate_class_nbr_1 = b.CLASS_NBR
AND a.strm = b.strm)
WHEN (a.relate_class_nbr_1 <> 0 AND a.relate_class_nbr_2 <> 0) THEN
(SELECT a.relate_class_nbr_2
FROM PS_ENRL_REQ_DETAIL a, PS_CLASS_TBL b
WHERE (b.SSR_COMPONENT = 'LAB' OR b.SSR_COMPONENT = 'REC')
AND a.relate_class_nbr_2 = b.CLASS_NBR
AND a.strm = b.strm)
END AS class_nbr
, RQ.strm
, RQ.institution
, 'D' --RQ.enrl_req_action
, C.SESSION_CODE
, C.SSR_COMPONENT
from PS_ENRL_REQ_DETAIL RQ, PS_CLASS_TBL C, PSOPRDEFN PS
WHERE RQ.strm = '1141'
AND RQ.STRM = C.STRM
AND (RQ.ENRL_REQ_DETL_STAT = 'M' OR RQ.ENRL_REQ_DETL_STAT = 'S')
AND RQ.institution = 'NT752'
AND RQ.EMPLID = PS.EMPLID
AND PS.OPRID =
(
	SELECT PP.OPRID
	FROM PSOPRDEFN PP
	WHERE PP.EMPLID = PS.EMPLID
	AND ROWNUM = '1'
)
AND (RQ.EMPLID, RQ.relate_class_nbr_1, RQ.STRM) NOT IN
(
	SELECT EMPLID, CLASS_NBR, STRM FROM PS_STDNT_ENRL
	WHERE STRM = '1141'
)
ORDER BY RQ.EMPLID;