Results 1 to 4 of 4

Thread: Get a resulset

  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Get a resulset

    Hi,

    DB2 V9.5 Z/OS

    KEYS

    P_LOC CHAR(3)
    NO_FIRST CHAR(3)


    Please find the below query which i tried to get the 'Expected Result' below
    Please let me know how to modify the qurey to get the 'Expected Result'
    Code:
    SELECT  
    
     P_LOC                             
    ,NO_FIRST                             
    ,VOL_ORDER
    ,CASE WHEN(RED_TYPE ='RED')
       THEN 'Y'
       ELSE 'N'
    END AS RED_TYPE
    ,CASE WHEN(GREEN_TYPE ='GREEN')
       THEN 'Y'
       ELSE 'N'
    END AS GREEN_TYPE
    ,CASE WHEN(BLUE_TYPE ='BLUE')
       THEN 'Y'
       ELSE 'N'
    END AS BLUE_TYPE
    ,CASE WHEN(ORANGE_TYPE ='ORANGE')
       THEN 'Y'
       ELSE 'N'
    END AS ORANGE_TYPE
    
    ,VOL_IND
    FROM(
    
    
    
    
    SELECT  
    
     P_LOC                             
    ,NO_FIRST                             
    ,VOL_TYPE AS RED_TYPE
    ,VOL_TYPE AS GREEN_TYPE
    ,VOL_TYPE AS BLUE_TYPE
    ,VOL_TYPE AS ORANGE_TYPE
    ,VOL_IND
    ,ROW_NUMBER()
      OVER (PARTITION BY
             P_LOC,
             NO_FIRST
        ORDER BY VOL_ORDER ASC) AS ROWNO1
    
    
    FROM BASE_TABLE  
    
    )
    WHERE 
    VOL_IND='PROCEED'
    Code:
    BASE_TABLE              
    
    P_LOC        NO_FIRST   VOL_ORDER    VOL_TYPE     VOL_IND
    
    AAA            111       1          RED            PROCEED          
    AAA            111       2          GREEN          STOP
    AAA            111       3          BLUE           PROCEED
    BBB            222       4          RED            STOP
    BBB            222       5          GREEN          PROCEED
    BBB            222       6          BLUE           PROCEED
    BBB            222       7          ORANGE         PROCEED
    EXPECTED RESULTSET
    Code:
    P_LOC        NO_FIRST       RED_TYPE    GREEN_TYPE     BLUE_TYPE   ORANGE_TYPE  VOL_IND 
    
    AAA            111            Y          N               Y          N            PROCEED
    BBB            222            N          Y               Y          Y            PROCEED

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is the result when executing your query ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    I am getting below result set

    Code:
    ACTUAL RESULT SET
    
    P_LOC        NO_FIRST       RED_TYPE    GREEN_TYPE     BLUE_TYPE   ORANGE_TYPE  VOL_IND 
    
    AAA            111            Y          N               N          N            PROCEED
    AAA            111            N          N               Y          N            PROCEED
    BBB            222            N          Y               N          N            PROCEED
    BBB            222            N          N               Y          N            PROCEED
    BBB            222            N          N               N          Y            PROCEED

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though I couldn't understand the reason to use nested-table-expression,
    please try

    Note: Tested on DB2 9.7 on Windows.
    It may be necessary to modify a CTE(to make test data) on z/OS.
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     base_table
    ( p_loc , no_first , vol_order , vol_type , vol_ind ) AS (
    VALUES
      ( 'AAA' , '111' ,  1 , 'RED'    , 'PROCEED' )         
    , ( 'AAA' , '111' ,  2 , 'GREEN'  , 'STOP'    )
    , ( 'AAA' , '111' ,  3 , 'BLUE'   , 'PROCEED' )
    , ( 'BBB' , '222' ,  4 , 'RED'    , 'STOP'    )
    , ( 'BBB' , '222' ,  5 , 'GREEN'  , 'PROCEED' )
    , ( 'BBB' , '222' ,  6 , 'BLUE'   , 'PROCEED' )
    , ( 'BBB' , '222' ,  7 , 'ORANGE' , 'PROCEED' )
    )
    SELECT p_loc
         , no_first
         , MAX(CASE vol_type
               WHEN 'RED'
               THEN 'Y'
               ELSE 'N'
               END  ) AS red_type
         , MAX(CASE vol_type
               WHEN 'GREEN'
               THEN 'Y'
               ELSE 'N'
               END  ) AS green_type
         , MAX(CASE vol_type
               WHEN 'BLUE'
               THEN 'Y'
               ELSE 'N'
               END  ) AS blue_type
         , MAX(CASE vol_type
               WHEN 'ORANGE'
               THEN 'Y'
               ELSE 'N'
               END  ) AS orange_type
         , vol_ind
     FROM  base_table
     WHERE vol_ind = 'PROCEED'
     GROUP BY
           p_loc
         , no_first
         , vol_ind
    ;
    ------------------------------------------------------------------------------
    
    P_LOC NO_FIRST RED_TYPE GREEN_TYPE BLUE_TYPE ORANGE_TYPE VOL_IND
    ----- -------- -------- ---------- --------- ----------- -------
    AAA   111      Y        N          Y         N           PROCEED
    BBB   222      N        Y          Y         Y           PROCEED
    
      2 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
  •