SQL query; how to make a SELECT field ignore WHERE criteria

From: <jadamson60_at_go.com>
Date: 3 Mar 2005 09:38:06 -0800
Message-ID: <1109871486.545829.57650@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 - 11:38:06 CST