A fun little ORDER BY problem (sorting char field)

Fumigator · #1 (**permalink**) 05-31-07, 18:16

I need to ORDER BY a column of type char(6) with values that are mostly numeric. I need those numeric values to sort numerically (12 after 2, etc), and the non-numeric values to (of course) sort alphabetically.

So, I wrote up the query like this:

Code:

SELECT KPBL_DESC_NUM
, KPBL_SEQ_NUM
, KPBL_BLK_VALUE
, KPBL_LOT_VALUE
, KPBL_LOT_THRU
, KPBL_LOT_TYPE
, CASE
  WHEN KPBL_BLK_VALUE > '000000' AND KPBL_BLK_VALUE < '999999'
  THEN CAST(KPBL_BLK_VALUE AS INT)
  ELSE
      0
END CASE
FROM P0230.RNDX_TMP_KPBL_V1
WHERE KPBL_DESC_NUM = 2960
ORDER BY
7

This does a fine job of sorting the numeric values properly, but all non-numeric values get lumped in together, unsorted (all those rows are sorted using that value of 0). The CASE function does not allow different data types returned from each branch, so I can't just return the original column value on the ELSE branch (that would be ideal).

Is there any other way I can get 'er done?

(This is on DB2 for z/OS v. 9, used in a COBOL/CICS application)

Fumigator · #2 (**permalink**) 05-31-07, 18:32

I just realized non-numeric values that begin with a number (such as "5AAA") will cause a -420 sqlcode error... so I think it may be back to the drawing board...

Fumigator · #3 (**permalink**) 05-31-07, 19:05

I resolved the issue of 5AAA generating a -420 but the query is starting to get a bit ridiculous:

Code:

DECLARE BLKLOTS-DATA INSENSITIVE SCROLL CURSOR FOR  
SELECT KPBL_SEQ_NUM                                 
      ,KPBL_BLK_VALUE                               
      ,KPBL_LOT_TYPE                                
      ,KPBL_LOT_VALUE                               
      ,KPBL_LOT_THRU                                
      ,CASE                                         
         WHEN                                       
          ((SUBSTR(KPBL_BLK_VALUE,1,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,1,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,1,1)  = ' ')      
      AND ((SUBSTR(KPBL_BLK_VALUE,2,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,2,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,2,1)  = ' ')      
      AND ((SUBSTR(KPBL_BLK_VALUE,3,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,3,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,3,1)  = ' ')      
      AND ((SUBSTR(KPBL_BLK_VALUE,4,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,4,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,4,1)  = ' ')      
      AND ((SUBSTR(KPBL_BLK_VALUE,5,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,5,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,5,1)  = ' ')      
      AND ((SUBSTR(KPBL_BLK_VALUE,6,1) >= '0' AND   
            SUBSTR(KPBL_BLK_VALUE,6,1) <= '9') OR   
            SUBSTR(KPBL_BLK_VALUE,6,1)  = ' ')      
      AND KPBL_BLK_VALUE <> ' '                     
         THEN CAST(KPBL_BLK_VALUE AS INT)           
         ELSE 0                                     
       END CASE                                     
  FROM P0230.RNDX_TMP_KPBL_V1                       
 WHERE KPBL_DESC_NUM = :KPBL-DESC-NUM               
ORDER BY 6, 4, 5

Hopefully someone will stop me and tell me the easy way to do this before I really get out of control here.

rahul_s80 · #4 (**permalink**) 06-01-07, 01:48

Hope this sample may help you :

select C.* from
(select row_number() over(order by integer(Awardingcode) ) as rownum, A.* from ABCD A where locate(' ',translate(Awardingcode,' ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))=0
union
select ( 10000+row_number() over(order by (Awardingcode) )) as rownum,B.* from ABCD B where locate(' ',translate(Awardingcode,' ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))<>0
) as C order by rownum

n_i · #5 (**permalink**) 06-01-07, 09:13

Well, if your strings, the ones that contain only digits, are padded with zeroes they will sort properly as strings, that is, '00002' will always come before '00012'. So, all you need to do is to pad "numeric" strings with zeroes.

Fumigator · #6 (**permalink**) 06-01-07, 16:21

Quote:

the ones that contain only digits, are padded with zeroes they will sort properly as strings

(sigh) yep, that would be really terrific... maybe I can look into fixing the gazillion existing messed up rows and validating the user and batch input . Thanks for the suggestion, it helps.

n_i · #7 (**permalink**) 06-02-07, 11:04

Dear Fumigator, the fact that you don't know how to do this doesn't mean it cannot be done. I hope your sarcastic wit helps you solve your problem. Good luck.

Hint: the ORDER BY clause can take not only column references but also expressions, e.g. the ones containing TRANSLATE(), LENGHT(), and REPEAT() functions.

Fumigator · #8 (**permalink**) 06-04-07, 12:16

My apologies n_i, my post was not meant in a sarcastic or unsincere manor. I meant every word-- I do appreciate your suggestion, and it does help. My (sigh) was pointed at the work it would be to convert the data, not at your suggestion to convert the data.

Unfortunately it's not as straight-forward as left-padding all numeric values with zeros, as the customer has data such as "A1, A20, A21, A3" that they expect to get sorted as "A1, A3, A20, A21" and 15 variations of that format.

n_i · #9 (**permalink**) 06-04-07, 15:16

So, you are saying is that, if the value has numbers in it it should be sorted in the numerical order, otherwise in alphabetical order? If that's the case, try this:

Code:

with t(KPBL_BLK_VALUE) as (values 'a00010', '55','2','12', 'test', 'abc1') 
select 
  KPBL_BLK_VALUE 
from t 
order by 
  case   
    when translate(upper(KPBL_BLK_VALUE),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ') <> ''     
    then cast(concat(repeat('0',6-length(ltrim(rtrim(translate(upper(KPBL_BLK_VALUE),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))))),rtrim(ltrim(translate(upper(KPBL_BLK_VALUE),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ')))) as char(6))   
    else     cast(KPBL_BLK_VALUE as char(6)) 
  end

Sure, it looks ugly but it seems to be working

Internet Services

Web Development

Digital Marketing

Consumer Tech