Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Connection two select from where blocks

Connection two select from where blocks

From: Spotly <spotly_at_aol.comEatSpam>
Date: 21 Feb 2000 04:44:41 GMT
Message-ID: <20000220234441.09896.00000754@ng-fu1.aol.com>


I'm trying to get the following fields: ONHAND, RENTED, ONTIME, OWED...and so on from the following. I know that the UNION isnt correct, but I cant seem to figure out how to do this. If someone could point me in the general direct I would appreciate it. Thanks for any help. Tim H.

SELECT COUNT(C.DVD_CODE)-COUNT(R.DVD_CODE) AS ONHAND FROM DVD_COPY C, DVD_RENTAL R
WHERE R.RENTAL_DATE IS NOT NULL
AND R.RETURN_DATE IS NULL
UNION
SELECT COUNT(R.DVD_CODE) AS RENTED
FROM DVD_RENTAL R
WHERE R.RENTAL_DATE IS NOT NULL
AND R.RETURN_DATE IS NULL
UNION
SELECT COUNT(R.DVD_CODE) AS ONTIME
FROM DVD_RENTAL R
WHERE R.RETURN_DATE = SYSDATE
AND (R.RETURN_DATE - R.RENTAL_DATE) < 2 UNION
SELECT COUNT(R.DVD_CODE) AS LATE
FROM DVD_RENTAL R
WHERE (R.RENTAL_DATE + 2) >= SYSDATE
AND R.RETURN_DATE IS NULL
UNION
SELECT SUM(I.PAYMENT) AS INCOME
FROM INVOICES I
WHERE I.TRANSACTION_DATE = SYSDATE
UNION
SELECT (SUM(TRUNC(SYSDATE-R.RENTAL_DATE))* F.LATE_FEE) AS OWED FROM DVD_RENTAL R, DVD_COPY C, DVD D, DVD_FEE_CATEGORY F WHERE R.DVD_CODE=C.DVD_CODE

AND C.DVD_ID = D.DVD_ID
AND D.FEE_CATEGORY = F.FEE_CATEGORY
AND R.RETURN_DATE IS NULL

GROUP BY F.LATE_FEE; Received on Sun Feb 20 2000 - 22:44:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US