-
Using the DECODE function in the ORDER BY clause
Hi everyone, I thought I had this working but it looks like the sort is happening lexicographically for every column, including my money column. I need that column to be sorted numerically. Can anyone tell me how I can force this statement to sort the "quarterlyearnings" column properly?
i.e. the numbers are currently sorting like:
1
100
10000
1000000
2500
300
3400
etc
And they need to sort in low-high/high-low order as you would expect amounts to.
Here is my PL/SQL
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?
thanks in advance,
Mike
-
use to_char
use to_char(to_number())for quarterlyearnings as in example
with a
as
(
Select TO_CHAR(to_number('1'), '9999999') rate from dual
union
Select TO_CHAR(to_number('300'), '999999') from dual
union
Select TO_CHAR(to_number('1300'), '999999') from dual
)
select * from a
order by rate
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|