Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: case with order by

    Hi;

    The below query should execute based on the value of SORT variable in the ORDER BY clause and produce the result sets like ASCENDING or DESCENDING order

    DB2 9.1 with z/OS

    Code:
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
        
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=111
      
    union
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=222
    
    order by
    
    
    case  when :sort='A-1' THEN t1.p_la
    END ASC
    ,CASE     
          WHEN :sort='A-2' THEN t1.p1_nn
    END ASC
    ,t1.p2_nn
    ,case
       WHEN :sort='A-3' THEN t2.t_des
    end asc
    ,case
    
     when :sort='D-1' THEN t1.p_la
    END DESC
    ,CASE     
          WHEN :sort='D-2' THEN t1.p1_nn
    END DESC
    ,t1.p2_nn
    ,case
       WHEN :sort='D-3' THEN t2.t_des
    end DESC
    Code:
    Table1
    
    p_la   p1_Nn    p2_nn          p_dD
    B11     12       34            aaa
    A11     30      334            aaa
    
    
    Table2
    
    t_no   p1_Nn    p2_nn       T_DES
    111      12       34        XXX  
    222      30      334        SSS
    Expected result

    when the ORT ='A-1' result set will be in ASC order of t1.p_la
    when the ORT ='A-2' result set will be in ASC order of t1.p1_nn,t1.p2_nn

    when the ORT ='D-1' result set will be in DESC order of t1.p_la
    when the ORT ='D-2' result set will be in DESC order of t1.p1_nn,t1.p2_nn

    But i am facing error like

    SQL0214N An expression in the ORDER BY clause in the following position, or
    starting with "1" in the "ORDER BY" clause is not valid. Reason code = "1".
    SQLSTATE=42822
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL0214N Reason code = "1".

    DB2 Version 9.1 for z/OS Codes
    1 The fullselect of the select-statement is not a
    subselect. Expressions are not allowed in the
    ORDER BY clause for this type of
    select-statement. This reason code occurs only
    when clause-type is ORDER BY.
    Try to enclose by an outer select statement, like...
    SELECT *
    FROM (
    /* your query without order by clause */
    ) AS s
    /* your order by clause with removing all column qualifiers("t1." and "t2.") */

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the help,working fine...

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I was executed the below query,but the DESC order not returning the proper values..

    Code:
    select 
           p_la
         , p1_nn
         , p2_nn 
         , p_dd
         ,t_des
    from(
    
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
        
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=111
      
    union
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=222
    ) as s
    
    order by
    
    
    case  when :sort='A-1' THEN p_la
    END ASC
    ,CASE     
          WHEN :sort='A-2' THEN p1_nn
    END ASC
    ,p2_nn
    ,case
       WHEN :sort='A-3' THEN t_des
    end asc
    ,case
    
     when :sort='D-1' THEN p_la
    END DESC
    ,CASE     
          WHEN :sort='D-2' THEN p1_nn
    END DESC
    ,p2_nn
    ,case
       WHEN :sort='D-3' THEN t_des
    end DESC
    Please help..

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There was ",p2_nn" before ",case WHEN :sort='A-3' ..." and DESC expressions in ORDER BY clause.

    So, If :sort was not 'A-1' nor 'A-2', then results would be ordered by p2_nn, even if :sort was 'A-3'.
    Last edited by tonkuma; 03-09-12 at 14:52. Reason: Add "case WHEN :sort='A-3' ..."

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    It is better to create the Dynamic Query then looking for solution for this one.

    You'll have the constant character string part and just add to this part string with order by depending on host variable "sort".

    It will work.

    Lenny

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Hi;

    I was executed the below query,but the DESC order not returning the proper values..

    Code:
    select 
           p_la
         , p1_nn
         , p2_nn 
         , p_dd
         ,t_des
    from(
    
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
        
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=111
      
    union
    SELECT t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
     FROM
           table1 t1
    inner join
          table t2
    on
    t1.p_la=t2.p_la
    and t1.p1_nn=t2.p1_nn
    and t1.p2_nn=t2.p2_nn
    where
     t2.t_no=222
    ) as s
    
    order by
    
    
    case  when :sort='A-1' THEN p_la
    END ASC
    ,CASE     
          WHEN :sort='A-2' THEN p1_nn
    END ASC
    ,p2_nn
    ,case
       WHEN :sort='A-3' THEN t_des
    end asc
    ,case
    
     when :sort='D-1' THEN p_la
    END DESC
    ,CASE     
          WHEN :sort='D-2' THEN p1_nn
    END DESC
    ,p2_nn
    ,case
       WHEN :sort='D-3' THEN t_des
    end DESC
    Sorry, I lost your original requirements.
    Expected result

    when the :SORT ='A-1' result set will be in ASC order of t1.p_la
    when the :SORT ='A-2' result set will be in ASC order of t1.p1_nn,t1.p2_nn

    when the :SORT ='D-1' result set will be in DESC order of t1.p_la
    when the :SORT ='D-2' result set will be in DESC order of t1.p1_nn,t1.p2_nn
    The code might be simplified like...
    Replaced whole of ORDER BY clause.
    Code:
    SELECT DISTINCT
           t1.p_la
         , t1.p1_nn
         , t1.p2_nn 
         , t1.p_dd
         , t2.t_des
     FROM  table1 t1
     INNER JOIN
           table  t2
      ON   t1.p_la  = t2.p_la
       AND t1.p1_nn = t2.p1_nn
       AND t1.p2_nn = t2.p2_nn
     WHERE t2.t_no IN (111 , 222)
     ORDER BY
           CASE :sort
           WHEN 'A-1' THEN
                p_la
           WHEN 'A-2' THEN
                p1_nn
           WHEN 'A-3' THEN
                t_des
           END  ASC
         , CASE :sort
           WHEN 'A-2' THEN
                p2_nn
           END  ASC
         , CASE :sort
           WHEN 'D-1' THEN
                p_la
           WHEN 'D-2' THEN
                p1_nn
           WHEN 'D-3' THEN
                t_des
           END  DESC
         , CASE :sort
           WHEN 'D-2' THEN
                p2_nn
           END  DESC
    ;
    Last edited by tonkuma; 03-10-12 at 00:06. Reason: Check "Disable smilies in text". Add two additional cases('A-3' and 'D-3'). Replace whole of ORDER BY clause.

Posting Permissions

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