Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38

    Unanswered: help with stored proc please

    The developers have produced the attached stored proc and are now asking for help to get a case statement in to the fetch first phrase.
    When I add this
    fetch first
    case in_count
    when '20' then '20 rows only ;'
    when '100' then '100 rows only;'
    end
    it objects to 'case' after fetch first.

    Note: i'm building this through Data Studio
    I also should have added that it is Db2 9.7 FP5 on Solaris
    Attached Files Attached Files
    Last edited by andy_mclauchlan; 03-19-12 at 02:15. Reason: more info

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an idea...
    (1) Put all query in a subquery, except ORDER BY clause.
    (2) Add "ROW_NUMBER() OVER(...) AS rnum" in the subquery.
    (3) Put ORDER BY clause of original query into OVER(...) clause in (2).
    (4) Add someting like "WHERE rnum <= IN_COUNT" in outer select statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that your ORDER BY clause might not work as you expected.
    Because, all THEN clause were constant like 'A.ASSESSMENT_ID asc' , 'A.ASSESSMENT_ID desc', so on.
    Then the ORDER BY clause is useless.

    I guessed that you might want to do like...
    Code:
     ORDER BY
           CASE IN_SORT_FIELD 
           WHEN 'assessIDasc'      THEN A.ASSESSMENT_ID
           WHEN 'dvaUinasc'        THEN C.DVA_UIN
           WHEN 'lNameasc'         THEN C.LAST_NAME
           WHEN 'fNameasc'         THEN C.FIRST_NAME
           WHEN 'dOBasc'           THEN C.DATE_OF_BIRTH
           WHEN 'dateReceivedasc'  THEN A.DATE_TRIGGER_RECEIVED
           WHEN 'statusasc'        THEN S.DESCRIPTION
           WHEN 'assignedOOasc'    THEN A.ASSIGNED_TO
           END  ASC
         , CASE IN_SORT_FIELD
           WHEN 'assessIDdesc'     THEN A.ASSESSMENT_ID
           WHEN 'dvaUindesc'       THEN C.DVA_UIN	
           WHEN 'lNamedesc'        THEN C.LAST_NAME
           WHEN 'fNamedesc'        THEN C.FIRST_NAME
           WHEN 'dOBdesc'          THEN C.DATE_OF_BIRTH
           WHEN 'dateReceiveddesc' THEN A.DATE_TRIGGER_RECEIVED
           WHEN 'statusdesc'       THEN S.DESCRIPTION
           WHEN 'assignedOOdesc'   THEN A.ASSIGNED_TO
           END  DESC

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is another example to choose sort sequence by a parameter.

    Value of ":sort" : output order
    'A-1' : p_la ASC
    'A-2' : p1_nn ASC , p2_nn ASC
    'A-3' : t_des ASC
    'D-1' : p_la DESC
    'D-2' : p1_nn DESC , p2_nn DESC
    'D-3' : t_des DESC

    Quote Originally Posted by tonkuma View Post
    ...

    The code might be simplified like...
    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
    ;

  5. #5
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    I have to admit your order by structure is better but it appears that the problem with the 'fetch first' phrase is that doesn't like the variable unless dynamic sql is used.
    We have tried another approach using temp tables and will be testing today
    thanks

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... the problem with the 'fetch first' phrase is that doesn't like the variable unless dynamic sql is used.
    Please try the way I showed, with modification for ORDER BY clause.
    Both(using ROW_NUMBER and modification for ORDER BY clause) are not mutual exclusive.
    Quote Originally Posted by tonkuma View Post
    Here is an idea...
    (1) Put all query in a subquery, except ORDER BY clause.
    (2) Add "ROW_NUMBER() OVER(...) AS rnum" in the subquery.
    (3) Put ORDER BY clause of original query into OVER(...) clause in (2).
    (4) Add someting like "WHERE rnum <= IN_COUNT" in outer select statement.

Posting Permissions

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