Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: problem with JOIN

    Hello
    I'd like to perform the following db request

    SELECT
    outlook.*,
    mrWED.WEEK_ENDING_DATE
    FROM
    CLAIMVW.OUTLOOK outlook

    LEFT JOIN (SELECT MAX(WEEK_ENDING_DATE) as WEEK_ENDING_DATE, EMP_SER_NUM, EMP_COMPANY_CODE, COUNTRY_CODE, WORK_ITEM_ID, GROUP_ID FROM CLAIMVW.OUTLOOK WHERE WEEK_ENDING_DATE<='2010-04-22' AND WEEK_ENDING_DATE>outlook.WEEK_ENDING_DATE GROUP BY EMP_SER_NUM, EMP_COMPANY_CODE, COUNTRY_CODE, WORK_ITEM_ID, GROUP_ID) mrWED ON
    mrWED.EMP_SER_NUM = outlook.EMP_SER_NUM AND
    mrWED.EMP_COMPANY_CODE = outlook.EMP_COMPANY_CODE AND
    mrWED.COUNTRY_CODE = outlook.COUNTRY_CODE AND
    mrWED.WORK_ITEM_ID = outlook.WORK_ITEM_ID AND
    mrWED.GROUP_ID = outlook.GROUP_ID

    But the problem is that I can't use outlook.WEEK_ENDING_DATE in the inner SELECT clause. I'm retrieving the following ERROR:
    [IBM][CLI Driver][DB2] SQL0206N "OUTLOOK.WEEK_ENDING_DATE" is not valid in the context where it is used. SQLSTATE=42703

    is there any workaround that will let me get the results I'm expecting?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TABLE keyword might be a trick.

    LEFT JOIN TABLE(SELECT ...

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    Thanks tonkuma
    It is a trick
    But after I added TABLE performance decreased by more than 10 times.
    I hope there must be some other way out

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples.
    Some performance improvements may be possible for these queries.

    Code:
    SELECT 
           outlook.*
         , mrWED.WEEK_ENDING_DATE
      FROM 
           CLAIMVW.OUTLOOK outlook
      LEFT JOIN
           TABLE
           (SELECT MAX(WEEK_ENDING_DATE) as WEEK_ENDING_DATE
                 , EMP_SER_NUM
                 , EMP_COMPANY_CODE
                 , COUNTRY_CODE
                 , WORK_ITEM_ID
                 , GROUP_ID
              FROM CLAIMVW.OUTLOOK mrWED
             WHERE mrWED.WEEK_ENDING_DATE <= '2010-04-22'
               AND mrWED.WEEK_ENDING_DATE >  outlook.WEEK_ENDING_DATE
               AND mrWED.EMP_SER_NUM      =  outlook.EMP_SER_NUM
               AND mrWED.EMP_COMPANY_CODE =  outlook.EMP_COMPANY_CODE
               AND mrWED.COUNTRY_CODE     =  outlook.COUNTRY_CODE
               AND mrWED.WORK_ITEM_ID     =  outlook.WORK_ITEM_ID
               AND mrWED.GROUP_ID         =  outlook.GROUP_ID
           ) mrWED
       ON  0 = 0
    ;

    Code:
    SELECT 
           outlook.*
         , (SELECT MAX(WEEK_ENDING_DATE) as WEEK_ENDING_DATE
                 , EMP_SER_NUM
                 , EMP_COMPANY_CODE
                 , COUNTRY_CODE
                 , WORK_ITEM_ID
                 , GROUP_ID
              FROM CLAIMVW.OUTLOOK mrWED
             WHERE mrWED.WEEK_ENDING_DATE <= '2010-04-22'
               AND mrWED.WEEK_ENDING_DATE >  outlook.WEEK_ENDING_DATE
               AND mrWED.EMP_SER_NUM      =  outlook.EMP_SER_NUM
               AND mrWED.EMP_COMPANY_CODE =  outlook.EMP_COMPANY_CODE
               AND mrWED.COUNTRY_CODE     =  outlook.COUNTRY_CODE
               AND mrWED.WORK_ITEM_ID     =  outlook.WORK_ITEM_ID
               AND mrWED.GROUP_ID         =  outlook.GROUP_ID
           )
      FROM 
           CLAIMVW.OUTLOOK outlook
    ;

  5. #5
    Join Date
    Apr 2010
    Posts
    7
    Thanks tonkuma

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Another solution

    How you shown your original query JOIN TABLE is required. Tonkuma gave you right direction. But another solution also possible:

    Code:
    SELECT 
    outlook.*,
    mrWED.WEEK_ENDING_DATE max_WEEK_ENDING_DATE
    FROM 
    CLAIMVW.OUTLOOK outlook
    
    left JOIN 
    (SELECT MAX(u1.WEEK_ENDING_DATE) as WEEK_ENDING_DATE, 
    u1.EMP_SER_NUM, u1.EMP_COMPANY_CODE, u1.COUNTRY_CODE, 
    u1.WORK_ITEM_ID, u1.GROUP_ID 
    FROM 
    CLAIMVW.OUTLOOK u1, CLAIMVW.OUTLOOK u2
    WHERE 
    u1.EMP_SER_NUM = u2.EMP_SER_NUM 
    AND 
    u1.EMP_COMPANY_CODE = u2.EMP_COMPANY_CODE 
    AND 
    u1.COUNTRY_CODE = u2.COUNTRY_CODE 
    AND 
    u1.WORK_ITEM_ID  = u2.WORK_ITEM_ID 
    AND 
    u1.GROUP_ID = u2.GROUP_ID
    and   
    u1.WEEK_ENDING_DATE <= '2010-04-22' 
    AND   
    u1.WEEK_ENDING_DATE  > u2.WEEK_ENDING_DATE 
    GROUP BY u1.EMP_SER_NUM, u1.EMP_COMPANY_CODE, 
    u1.COUNTRY_CODE, u1.WORK_ITEM_ID, u1.GROUP_ID) mrWED 
    
    ON 
    mrWED.EMP_SER_NUM = outlook.EMP_SER_NUM 
    AND 
    mrWED.EMP_COMPANY_CODE = outlook.EMP_COMPANY_CODE 
    AND 
    mrWED.COUNTRY_CODE = outlook.COUNTRY_CODE 
    AND 
    mrWED.WORK_ITEM_ID = outlook.WORK_ITEM_ID 
    AND 
    mrWED.GROUP_ID = outlook.GROUP_ID
    Lenny

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How did you come up with the 10x slower if you original query had a syntax problem? What are you comparing?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2010
    Posts
    7
    I executed the query without the condition highlighted in blue.
    It took about 2 secs to execute, while TABLE decorated query was executing for about 25 secs.

    It was not a big deal to understand that smth was going wrong.

  9. #9
    Join Date
    Apr 2010
    Posts
    7
    Thanks for your solution Lenny

Posting Permissions

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