Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: RANKING function instead of GROUP BY

    Hello,

    Working with DB2 v10 on zOS. My query is returning a duplicate row in the output as follows:

    Col 1 Col 2 Col3 Col 4 Col 5 Col6
    a01 123 Y EX ABC DEF

    Query consists of an outer query with two INNER joins which is LEFT joined to another query which is in turn is also LEFT joined to another query with GROUP BY joins in the two LEFT join queries:
    Code:
    select cod.DECODED_FULL_DESC AS SELL_PLAN
                    ,a.UNIQUE_ITEM_CODE
                    ,b.INCLUSION_IND 									
                    ,ses.SCHEDULE_RESTRICTION_CODE						
                    ,cod.ENCODED_CODE
                    ,ses.SCHEDULE_CODE                                         
                    from CST.COD_COLUMN_DECODE cod           
                    
                    inner join cst.SES_SELL_SCHEDULE ses
                    on ses.SCHEDULE_CODE=cod.ENCODED_CODE
                    and COD.DATABASE_ALIAS = 'XX'
                    AND COD.TABLE_NAME_PREFIX = 'AAA'
                    AND COD.COLUMN_NAME = '1 SCHEDULE'
                    AND COD.DECODED_SHORT_DESC = 'CONTRACT'
                    
                    inner join iit.BUS_BUSINESS_XREF bus
                    on ses.BUSINESS_CODE = bus.NEW_BUSINESS_CODE
                    and ses.COMPANY_CODE = bus.COMPANY_CODE
                    and bus.BUSINESS_CODE = 12
                    
                    left join (
                    
                    SELECT RCA.SELL_SCHED_CODE
                    ,ITX.UNIQUE_ITEM_CODE
                    FROM IMT.ITX_ITEM_CROSS_REF ITX                
                    INNER JOIN CST.RCA_REG_SELL_CALC RCA
                    ON RCA.BUSINESS_CODE = ITX.BUSINESS_CODE
                    AND RCA.UNIQUE_ITEM_CODE = ITX.UNIQUE_ITEM_CODE
                    AND RCA.COMPO_NBR = 123
                    AND CURRENT DATE BETWEEN RCA.START_DATE AND RCA.STOP_DATE
                    WHERE ITX.FACILITY_CODE = 'ABC'
                    AND ITX.BUSINESS_CODE = 12
                    AND ITX.ITEM_CODE = 123 
    
                    GROUP BY RCA.SELL_SCHED_CODE
                    ,ITX.UNIQUE_ITEM_CODE) a
                    on cod.ENCODED_CODE = a.SELL_SCHED_CODE
                    
                    left join (
                    SELECT  isi.SCHEDULE_CODE
                            ,isi.INCLUSION_IND								
                            ,MAX(isi.CHANGE_TIME_STAMP)               
                    FROM cst.ISI_ITEM_SCHEDULE_INCLUSION isi                
                    WHERE isi.BUSINESS_CODE = 12                
                    and isi.UNIQUE_ITEM_CODE = 12345
                    AND CURRENT DATE BETWEEN isi.START_DATE AND isi.STOP_DATE
                    
                    GROUP BY isi.SCHEDULE_CODE, isi.INCLUSION_IND) b		--apply ranking function? 	
                    --SELECT ROWNUMBER() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX                
                    on cod.ENCODED_CODE = b.SCHEDULE_CODE
                   
                   group by 
                   cod.DECODED_FULL_DESC
                   ,a.UNIQUE_ITEM_CODE               
                   ,b.INCLUSION_IND          								--apply ranking function?	
                   --SELECT ROWNUMBER() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX                    
                   ,ses.SCHEDULE_RESTRICTION_CODE 							--apply ranking function?
                   --SELECT ROWNUMBER() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX	               
                   ,ses.SCHEDULE_CODE
                   ,b.SCHEDULE_CODE
                   ,cod.ENCODED_CODE
    Please note that there are comments where I think we would want to apply the RANKING functions. If you keep the column in group by obviously it will return two rows, so this has been a suggested solution.

    Another suggestion has been to separate each query so they are on the same level INNER, LEFT, LEFT, but the first suggestion came from a resource that has been working the the data longer than the other.

    Hope this all makes sense.

    Thanks,
    Buster

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Quick question on:
    Code:
    left join (
                    SELECT  isi.SCHEDULE_CODE
                            ,isi.INCLUSION_IND								
                            ,MAX(isi.CHANGE_TIME_STAMP)
    why are you performing this? you aren't selecting this timestamp in your final product. I think what you may actually be looking for is the most recent inclusion_ind??? If so, then going about it incorrectly. should be stating you want the time stamp = to the max timestamp within your where clause.
    Dave

Posting Permissions

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