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

jadamson60_at_go.com wrote:

> 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

One could spend a lot of time worrying about it ... or ... use UNION or UNION ALL. Write two queries and join the result sets.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)