Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Connection two select from where blocks
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