Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2007
    Posts
    24

    Unanswered: A fun little ORDER BY problem (sorting char field)

    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)

  2. #2
    Join Date
    Mar 2007
    Posts
    24
    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...

  3. #3
    Join Date
    Mar 2007
    Posts
    24
    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.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2007
    Posts
    24
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Mar 2007
    Posts
    24
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Sep 2013
    Posts
    1
    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.
    I did the first part of this like so:

    SELECT SOMETHING FROM (
    Select DISTINCT CHAR(numerical_value) AS SOMETHING, numerical_value
    FROM TABLE_NAME
    ORDER BY numerical_value ASC
    ) t

    This sorts the data in the proper format without returning any values as actual numbers. You still get a great CHAR format. I do realize this does not fully answer your question, but it may help the random Googler if they stumble across this thread when they face a similar issue.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Fumigator View Post
    ...
    ...

    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.
    What data formats are possible?
    What data formats are impossible?

    For example, the following data are possible?
    How to order all possible data in the example?

    Code:
    KPBL_BLK_VALUE FORMAT NOTE1               possible?
    -------------- ------ ------------------- ---------
    5AAA           naaa__  trailing blank(s).  Yes     
    A1             an____  trailing blank(s).  Yes     
    A20            ann___  trailing blank(s).  Yes     
    A21            ann___  trailing blank(s).  Yes     
    A3             an____  trailing blank(s).  Yes     
    /* added by n_i */
    a00010         annnnn  no trailing blank.  ???     
    55             nn____  trailing blank(s).  ???     
    2              n_____  trailing blank(s).  ???     
    12             nn____  trailing blank(s).  ???     
    test           test__  trailing blank(s).  ???     
    abc1           abcn__  trailing blank(s).  ???     
    /* added by me */
    30AA           nnaa__  trailing blank(s).  ???     
    30AAAA         nnaaaa  no trailing blank.  ???     
    100AAA         nnnaaa  no trailing blank.  ???     
    A100A          annna_  trailing blank(s).  ???     
    30A2           nnan__  trailing blank(s).  ???     
    30A10          nnann_  trailing blank(s).  ???     
    30A10A         nnanna  no trailing blank.  ???     
    30A1AA         nnanaa  no trailing blank.  ???
    Last edited by tonkuma; 09-28-13 at 16:17. Reason: Put Yes in "possible?" column for your supplied data.

  12. #12
    Join Date
    Nov 2004
    Posts
    67
    On DB2 LUW I've had good results with

    ...
    ORDER BY
    length(alphanumeric_field),
    alphanumeric_field
    ...

    without any padding.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tested CCMF's idea on DB2 9.7.5 for Windows by using my last sample data.

    Fumigator,
    Are these your required results?

    Example 1: CCMF's original.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_test_data AS (
    SELECT CHAR(KPBL_BLK_VALUE , 6) AS alphanumeric_field
     FROM  (VALUES
               '5AAA'
             , 'A1' , 'A20' , 'A21' , 'A3'
             , 'a00010' , '55' , '2' , '12' , 'test' , 'abc1'
             , '30AA'
             , '30AAAA'
             , '100AAA'
             , 'A100A'
             , '30A2'
             , '30A10'
             , '30A10A'
             , '30A1AA'
           ) s(KPBL_BLK_VALUE)
    )
    SELECT *
     FROM  sample_test_data
     ORDER BY
           length(alphanumeric_field),
           alphanumeric_field
    ;
    ------------------------------------------------------------------------------
    
    ALPHANUMERIC_FIELD
    ------------------
    100AAA            
    12                
    2                 
    30A10             
    30A10A            
    30A1AA            
    30A2              
    30AA              
    30AAAA            
    55                
    5AAA              
    A1                
    A100A             
    A20               
    A21               
    A3                
    a00010            
    abc1              
    test              
    
      19 record(s) selected.

    Example 2: Added RTRIM
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_test_data AS (
    SELECT CHAR(KPBL_BLK_VALUE , 6) AS alphanumeric_field
     FROM  (VALUES
               '5AAA'
             , 'A1' , 'A20' , 'A21' , 'A3'
             , 'a00010' , '55' , '2' , '12' , 'test' , 'abc1'
             , '30AA'
             , '30AAAA'
             , '100AAA'
             , 'A100A'
             , '30A2'
             , '30A10'
             , '30A10A'
             , '30A1AA'
           ) s(KPBL_BLK_VALUE)
    )
    SELECT *
     FROM  sample_test_data
     ORDER BY
           length( RTRIM(alphanumeric_field) ),
           alphanumeric_field
    ;
    ------------------------------------------------------------------------------
    
    ALPHANUMERIC_FIELD
    ------------------
    2                 
    12                
    55                
    A1                
    A3                
    A20               
    A21               
    30A2              
    30AA              
    5AAA              
    abc1              
    test              
    30A10             
    A100A             
    100AAA            
    30A10A            
    30A1AA            
    30AAAA            
    a00010            
    
      19 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •