Use two union all 's
The first union has the where clause that you want to
and the second one doesn't.
Andreas
--
WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup
<jadamson60_at_go.com> wrote in message
news:1109871486.545829.57650_at_g14g2000cwa.googlegroups.com...
> Hi
>
> I have an SQL query that I want to modify so that one of the fields
> that I am calling with the SELECT ignores one of the WHERE clause's
> (but the others still use it). I was thinking an OUTER JOIN might do it
> but it doesn't in this case; the WHERE clause still restricts the
> records outputted. What's the best approach to make this column
> independant from the specific WHERE clause (there are two comments (--)
> to indicate the fields) ? I'm using Oracle 9i:
>
> SELECT
> COUNT(Z35_EVENT_TYPE) LOANS,
> Z13_USER_DEFINED_3 BIB,
> COUNT(DISTINCT(Z30_REC_KEY)) CP, --this field ind. of z35_event_date
> Z30_CALL_NO CALLNO,
> Z30_COLLECTION COLLECTION
> FROM
> Z35 FULL OUTER JOIN Z30 ON (Z35.Z35_REC_KEY ||
> LPAD(Z35.Z35_ITEM_SEQUENCE, 6, '0')) = Z30.Z30_REC_KEY JOIN Z13
> ON SUBSTR(Z30_REC_KEY,1,9) = Z13_REC_KEY
> WHERE
> Z30.Z30_CALL_NO LIKE 'SK 6.9 PRE' AND
> Z30_COLLECTION IN ('O','Q','RES','RESQ') AND
> Z30_SUB_LIBRARY='JBM' AND
> Z35_EVENT_TYPE IN (50,53) AND
> Z35_EVENT_DATE BETWEEN '20020901' AND '20030306' --field to ignore
> GROUP BY
> Z35_REC_KEY,
> Z35_EVENT_TYPE,
> Z30_CALL_NO,
> Z30_COLLECTION,
> Z13_USER_DEFINED_3
> ORDER BY COUNT(Z35_EVENT_TYPE) DESC;
>
> thanks in advance
> Jim
>
Received on Thu Mar 03 2005 - 13:37:24 CST
