Database Reference
In-Depth Information
vSECONDS := TO_NUMBER(SUBSTR(pTIME,vSPLIT+1,vLEN-vSPLIT));
RETURN vHOURS+(vSECONDS/60);
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/
Here is the query:
COLUMN CD FORMAT A24 HEADING "CD";
COLUMN SONG FORMAT A40 HEADING "Song";
COLUMN TIME FORMAT 99990.00 HEADING "Time(mins)";
SELECT M.TITLE AS CD, S.TITLE AS SONG,
SUM(GETTIME(S.PLAYING_TIME)) AS TIME
FROM MUSICCD M JOIN CDTRACK T USING (MUSICCD_ID)
JOIN SONG S USING (SONG_ID)
WHERE S.PLAYING_TIME IS NOT NULL AND M.TITLE LIKE 'Th%'
GROUP BY ROLLUP (M.TITLE, S.TITLE);
The query in Figure 11.17 shows rollup grouping on the title of a CD
and the title of its song. Because a song is at the lowest level of grouping
detail, no subtotal is shown for each song. Do you see the subtotals for both
CDs plus the grand total at the end?
Note:
The function GETTIME() is custom written and can be found in
Chapter 24. GETTIME() is necessary to convert a string value of minutes
and seconds to a real (floating-point) number value. The use of the GET-
TIME() function shows that custom functions can be used in GROUP
BY clauses.
11.4.3.2
The CUBE Clause
Unlike the ROLLUP clause, which can be used to produce subtotals and
grand totals for subset groups, the CUBE clause can be used to produce all
combinations for a GROUP BY expression. The CUBE clause can be used
to create three-dimensional cross-tabulation reports. The result of the fol-
lowing query is shown in Figure 11.18.
COLUMN COUNTRY FORMAT A10 HEADING "Country";
COLUMN STATE FORMAT A10 HEADING "State";
