creating view from multiple select statements
740338Dec 10 2009 — edited Dec 10 2009I am having some trouble making a view and wondered if you could help.
The view is going to contain two columns, Number_of_Rentals & Previous_rentals.
i want to put the result of one select statement in the first column and the result of another in the other column.
How do you go about doing this?
this is what i currently have:
CREATE VIEW V_REPORT (NUMBER_OF_RENTALS, PREVIOUS_RENTALS)
AS
(SELECT COUNT(RENTAL_ID)
FROM V_GENRE
WHERE DATE_IN > TO_DATE('1-APR-&DATEB', 'DD-MON-YYYY')
AND DATE_IN < TO_DATE('31-MAR-&DATEE', 'DD-MON-YYYY')),
(SELECT COUNT (RENTAL_ID)
FROM V_GENRE
WHERE DATE_IN > ADD_MONTHS(TO_DATE('1-APR-&DATEB', 'DD-MON-YYYY'),-12)
AND DATE_IN < ADD_MONTHS(TO_DATE('31-MAR-&DATEE', 'DD-MON-YYYY'),-12));
Also as you can see i have 2 values that occur twice (&DATEE and &DATEB) but it sees them as 4 different values, how do i get it to see them as one value?
Many thanks.