Database Reference
In-Depth Information
6.3.2
Sorting by Expression
Now let's make the query shown in Figure 6.8 a little more complex, utiliz-
ing an expression in the ORDER BY clause for the same query. In Figure
6.8, the PLAYING_TIME column (position 2) was sorted as a string. A
more sensible result would require an expression converting that string to a
number. Expressions can include built-in SQL functions or even user-
defined functions. This is the query used in Figure 6.8.
1
Note:
Using expressions in the ORDER BY clause can hurt performance.
SELECT RECORDING_DATE, PLAYING_TIME, TITLE
FROM SONG
WHERE TITLE LIKE '%a%'
AND TITLE LIKE '%e%'
AND TITLE LIKE '%i%'
ORDER BY 2 NULLS FIRST, 3 DESC, 1;
This is the change for the ORDER BY clause of the query sorting the
PLAYING_TIME column numerically, using a straightforward expression:
ORDER BY
TO_NUMBER(SUBSTR(PLAYING_TIME,1
,INSTR(PLAYING_TIME,':')-1))
+ TO_NUMBER(SUBSTR(PLAYING_TIME
,INSTR(PLAYING_TIME,':')+1))/60
NULLS FIRST, 3 DESC, 1;
Straightforward, I said. That is just nasty! That example does not really
look straightforward, now does it? Let's make this a little easier, or per-
haps just a little better organized, utilizing a user-defined function (see
Chapter 24).
CREATE OR REPLACE FUNCTION GETTIME (pTIME IN VARCHAR2)
RETURN NUMBER IS
vSPLIT INTEGER DEFAULT 0;
vHOURS INTEGER DEFAULT 0;
vSECONDS INTEGER DEFAULT 0;
