Code:
PROCEDURE retrieve_cursor (
p_year IN tax_filing_detail.YEAR%TYPE,
p_qtr IN tax_filing_detail.qtr%TYPE,
p_emplr_id IN tax_filing_detail.emplr_id%TYPE,
p_sort_expr IN VARCHAR2 default 'lastname',
p_sort_dir IN VARCHAR2 default '0',
p_cursor OUT filing_detail_cursor
)
IS
BEGIN
IF (p_sort_dir = '0') THEN
OPEN p_cursor FOR
SELECT ID AS ID, ssn AS ssn, first_name AS firstname, middle_name AS middlename, last_name AS lastname,
YEAR AS YEAR, qtr AS quarter, seasonal_ind AS seasonalindicator, recd_dt AS datereceived,
emplr_id AS employerid, confirmation_id AS confirmationid,
qtr_earnings AS quarterlyearnings,
weeks_wkd, hrs_wkd, trans_cnt
FROM tax_filing_detail
WHERE (emplr_id = p_emplr_id AND YEAR = p_year AND qtr = p_qtr)
ORDER BY
DECODE(p_sort_expr, 'SSN', ssn,
'Firstname' , firstname,
'MiddleName', middlename,
'LastName', lastname,
'SeasonalIndicator', seasonalindicator,
'QuarterlyEarnings', quarterlyearnings,
lastname) ASC;
ELSE
OPEN p_cursor FOR
SELECT ID AS ID, ssn AS ssn, first_name AS firstname, middle_name AS middlename, last_name AS lastname,
YEAR AS YEAR, qtr AS quarter, seasonal_ind AS seasonalindicator, recd_dt AS datereceived,
emplr_id AS employerid, confirmation_id AS confirmationid,
qtr_earnings AS quarterlyearnings,
weeks_wkd, hrs_wkd, trans_cnt
FROM tax_filing_detail
WHERE (emplr_id = p_emplr_id AND YEAR = p_year AND qtr = p_qtr)
ORDER BY
DECODE(p_sort_expr, 'SSN', ssn,
'Firstname' , firstname,
'MiddleName', middlename,
'LastName', lastname,
'SeasonalIndicator', seasonalindicator,
'QuarterlyEarnings', quarterlyearnings,
lastname) DESC;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END retrieve_cursor;
Is there some way to force the decode to sort numerically depending on which statement is being evaluated?