Re: Query results change

jwilliam_at_aglresources.com (James A. Williams) wrote in message news:<5003a2b9.0411180722.52859e99_at_posting.google.com>...
> The first query is the current query and gives the user the results
> desired. It was required to provide a function the app was not
> designed for.
>
> A user wants the column 'A.DEAD_MIN_AVAIL as "AvailGrayMin"' added. Of
> course this changes the results of the distinct. They would like the
> same rows returned as before with the column added. No criteria other
> than that to use:
>
> --
>
>
> SELECT * FROM (
> (SELECT DISTINCT APPOINTMENT_DTE AS "ApptDate", AW.START_TIME as
> "StartTime", AW.END_TIME as "EndTime", A.LEVEL_NBR as "ApptWindow",
> A.SEQ_NBR as "ApptSequence"
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ('03','06') AND (A.MIN_AVAIL <= 0 AND A.DEAD_MIN_AVAIL >= 15 or
> MIN_AVAIL >= 15) AND A.GEO_AREA_CD = '3HBA0' AND A.JOB_GROUP =
> 'FTECH'
> UNION
> (SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ( '03','06' ) AND (A.MIN_AVAIL <= 0 AND A.DEAD_MIN_AVAIL >= 15 or
> MIN_AVAIL >= 15) AND A.GEO_AREA_CD = '3H000' AND A.JOB_GROUP =
> 'FTECH'
> MINUS
> SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW,AT_EXCEPTION AE
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.GEO_AREA_CD = AE.GEO_AREA_CD AND AE.EXCEPTION_DTE =
> A.APPOINTMENT_DTE AND A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR =
> AW.SEQ_NBR AND A.GEO_AREA_CD = '3HBA0' AND AW.LEVEL_NBR IN
> ('03','06')))
> MINUS
> SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE = TO_CHAR(SYSDATE,'YYYYMMDD') AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ('03','06') AND (CAL_ETIME_MIN -
> (TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 +
> TO_NUMBER(TO_CHAR(SYSDATE,'MI'))) < '195') AND GEO_AREA_CD = '3H000'
> ORDER BY 1 ASC,4 ASC,5 ASC )
> AGG WHERE ROWNUM <= '100
>
> --
>
> SELECT * FROM (
> (SELECT DISTINCT APPOINTMENT_DTE AS "ApptDate", AW.START_TIME as
> "StartTime", AW.END_TIME as "EndTime", A.LEVEL_NBR as "ApptWindow",
> A.SEQ_NBR as "ApptSequence", A.DEAD_MIN_AVAIL as "AvailGrayMin"
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ('03','06') AND (A.MIN_AVAIL <= 0 AND A.DEAD_MIN_AVAIL >= 15 or
> MIN_AVAIL >= 15) AND A.GEO_AREA_CD = '3HBA0' AND A.JOB_GROUP =
> 'FTECH'
> UNION
> (SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR, A.DEAD_MIN_AVAIL
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ( '03','06' ) AND (A.MIN_AVAIL <= 0 AND A.DEAD_MIN_AVAIL >= 15 or
> MIN_AVAIL >= 15) AND A.GEO_AREA_CD = '3H000' AND A.JOB_GROUP =
> 'FTECH'
> MINUS
> SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR, A.DEAD_MIN_AVAIL
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW,AT_EXCEPTION AE
> WHERE APPOINTMENT_DTE BETWEEN '20041116' AND '20041201' AND
> A.GEO_AREA_CD = AE.GEO_AREA_CD AND AE.EXCEPTION_DTE =
> A.APPOINTMENT_DTE AND A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR =
> AW.SEQ_NBR AND A.GEO_AREA_CD = '3HBA0' AND AW.LEVEL_NBR IN
> ('03','06')))
> MINUS
> SELECT DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME,
> A.LEVEL_NBR, A.SEQ_NBR, A.DEAD_MIN_AVAIL
> FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
> WHERE APPOINTMENT_DTE = TO_CHAR(SYSDATE,'YYYYMMDD') AND
> A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR
> IN ('03','06') AND (CAL_ETIME_MIN -
> (TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 +
> TO_NUMBER(TO_CHAR(SYSDATE,'MI'))) < '195') AND GEO_AREA_CD = '3H000'
> ORDER BY 1 ASC,4 ASC,5 ASC )
> AGG WHERE ROWNUM <= '100

James, without spending time studying your query in depth the idea that perhaps, you can make the first query an inline view and join the results to the source table to get the additional data, comes to mind.

HTH -- Mark D Powell -- Received on Fri Nov 19 2004 - 09:32:46 CST