Charllotte,
Why don't you try using an outer join on rq_grp_tbl? Something like this:
.............
and (b.rqrmnt_group *= f.rqrmnt_group
and f.effdt =
( SELECT MAX(f1.EFFDT)
FROM ps_rq_grp_tbl f1
WHERE f1.rqrmnt_group = f.rqrmnt_group
AND f1.EFFDT <= sysdate
and f.eff_status = 'A'));
Debbie
>
> Date: Thu, 5 Jul 2001 14:07:58 -0700
> From: Charllotte Hurt <CHurt@YAR.MIRACOSTA.CC.CA.US>
> Subject: help with sql
>
> I am working with an sql statement which I need to modify in order to
pick up additional information in another table. (The tables involved are
from the PeopleSoft Student Administration product and this sql statement is
a view.) The outcome I want from this is all the fields currently being
selected; but, in addition, I need the descr254a field from the rq_grp_tbl
for those courses which have a rqrmrnt_group and also those courses which
don't have a rqrmnt_group. The sql below is the original with my additions
in bold. I also have put crse_ids to simplify my testing, one has a
rqrmnt_group and one does not. My problem is that I receive one row with
all the necessary information for the course id which has the rqrmnt_group,
but for the one which doesn't I am getting back every row from the
rq_grp_tbl (because of my not exists). I only want one row returned for
each course_id in my select. I am getting back 243 rows, one for the course
with the rqrmnt_group and 242 rows !
> for the one which has no rqrmnt_group (242 rows is the totat count of the
rq_grp_tbl).
>
> I would really appreciate some help on this. Any suggestions? I need to
do this in the sql--not in an sqr. (Sqr would allow loops=1).
>
> Thanks in advance for your assistance.
>
> SELECT
> A.CRSE_ID
> ,A.EFFDT
> ,B.INSTITUTION
> ,B.SUBJECT
> ,B.CATALOG_NBR
> ,A.EFF_STATUS
> ,A.UNITS_MINIMUM
> ,A.UNITS_MAXIMUM
> ,A.COMPONENT
> ,A.COURSE_TITLE_LONG
> ,C.DESCRFORMAL
> ,D.DESCR
> ,E.XLATLONGNAME
> ,A.DESCRLONG
> ,f.descr254a
> ,b.rqrmnt_group
> FROM
> PS_CRSE_CATALOG A,
> PS_CRSE_OFFER B,
> PS_SUBJECT_TBL C,
> PS_INSTITUTION_TBL D,
> XLATTABLE E,
> ps_rq_grp_tbl f
> WHERE A.CRSE_ID = B.CRSE_ID AND
> a.crse_id in ('001750','000322') and
> A.EFFDT =
> ( SELECT MAX(Z.EFFDT)
> FROM PS_CRSE_CATALOG Z
> WHERE A.CRSE_ID = Z.CRSE_ID
> AND Z.EFFDT <= sysdate)
> AND
> A.EFF_STATUS = 'A' AND
> A.EFFDT = B.EFFDT AND
> B.CATALOG_PRINT = 'Y' AND
> B.INSTITUTION = C.INSTITUTION AND
> C.EFFDT =
> ( SELECT MAX(X.EFFDT)
> FROM PS_SUBJECT_TBL X
> WHERE C.INSTITUTION = X.INSTITUTION
> AND C.SUBJECT = X.SUBJECT
> AND X.EFFDT <= sysdate) AND
> B.INSTITUTION = D.INSTITUTION AND
> D.EFFDT =
> ( SELECT MAX(Y.EFFDT)
> FROM PS_INSTITUTION_TBL Y
> WHERE D.INSTITUTION = Y.INSTITUTION
> AND Y.EFFDT <= sysdate) AND
> B.SUBJECT = C.SUBJECT AND
> B.INSTITUTION = D.INSTITUTION AND
> E.FIELDNAME = 'COMPONENT' AND
> E.LANGUAGE_CD =
> (SELECT LANGUAGE_CD FROM PSOPTIONS)
> AND
> A.COMPONENT = E.FIELDVALUE AND
> E.EFFDT =
> ( SELECT MAX(W.EFFDT)
> FROM XLATTABLE W
> WHERE E.FIELDNAME = W.FIELDNAME
> AND E.LANGUAGE_CD = W.LANGUAGE_CD
> AND E.FIELDVALUE = W.FIELDVALUE
> AND W.EFFDT <= sysdate)
> and (b.rqrmnt_group = f.rqrmnt_group
> and f.effdt =
> ( SELECT MAX(f1.EFFDT)
> FROM ps_rq_grp_tbl f1
> WHERE f1.rqrmnt_group = f.rqrmnt_group
> AND f1.EFFDT <= sysdate)
> and f.eff_status = 'A' or
> (not exists (select 'x' from ps_rq_grp_tbl f2
> where f2.rqrmnt_group = b.rqrmnt_group)));
>
> ------------------------------
_______________________________________________________
Send a cool gift with your E-Card
http://www.bluemountain.com/giftcenter/