Using the DECODE function in the ORDER BY clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Using the DECODE function in the ORDER BY clause

  1. #1
    Join Date
    Mar 2009
    Posts
    1

    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

  2. #2
    Join Date
    Dec 2000
    Posts
    126

    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
  •  



Click Here to Expand Forum to Full Width