Re: help with sql

[Arjan Hoornstra <ahoor@YAHOO.COM>]

Charlotte,

You problem seems to be you 'f' table. This table is not joined because
of the or clause, causing a carthesian product.

Look at this part (I edited the layout for readability):

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
                      )
         )
    )

Because the 'or' is on the same level as the 'and' (twice)  and the
join it overrules everything.
So you would have to use an outerjoin, but because of the 'max' clause
in the query and the descr254a column in the select that is not
possible.

Depending on your database system I think the only way to solve this is
create a temporary table in which you insert the contents of table f
with only the max(EFFDT) of each group, and then you can use the
outerjoin...

Something like:

select 'EFFDT' = MAX(f1.EFFDT), rqrmnt_group, eff_status, descr254a
into   #ps_rq_grp_tbl
from   ps_rq_grp_tbl
where  EFFDT <= sysdate
and    eff_status = 'A'
group by rqrmnt_group, eff_status, descr254a

(eff_status is optional if you only need the 'A' values)

Now in your query replace ps_rq_grp_tbl with #ps_rq_grp_tbl as your 'f'
table in the FROM clause, and change the join to:

and b.rqrmnt_group *= f.rqrmnt_group
and f.eff_status = 'A'

This should work for Sybase and MS-sql, for Oracle you may need a
workaround for the #table.

Hope this helps, let me know...

Arjan


--- Charllotte Hurt <CHurt@YAR.MIRACOSTA.CC.CA.US> wrote:
> 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)));


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/