DB2 SQL. Using a Case Statement on an Order By Clause with an alpha and numeric field-CodePudding

I am attempting to use a case statement in my order by clause but am unsuccessful because one field is char and one field is numeric. If I put a Char() around the numeric, I don't get the sort I need because it is three byte numeric, 1, 2, 3. Which sorts in char() like 1, 10, 100, 2, etc Is there a way to pad with zeros so that it sorts properly? Code:

Order by                                  
  Case                                    
     When :SortBy = 'I' then LProd    <-- Char
     When :SortBy = 'L' then Char(LLine)  <-- Numeric
  End;

Does anyone have an idea on how to do this?

CodePudding user response：

try DIGITS()

Order by                                  
  Case                                    
     When :SortBy = 'I' then LProd    <-- Char
     When :SortBy = 'L' then digits(LLine)  <-- Numeric
  End;

CodePudding user response：

You can use cte(common table expression) to first order by LProd and give it a rank(numeric value). And then select from that cte and order with what ever you need because both will be numeric.

with cte as(select id, rank() over (order by LProd) as LProd_ord, LProd, LLine from test)
select id, LProd, LLine
from cte
order by Case                                    
     When 'I' = 'I' then LProd_ord    
     When 'I' = 'L' then LLine
  End ;

Here is a demo