problem with JOIN

archie.by · #1 (**permalink**) 04-22-10, 09:31

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.

tonkuma · #2 (**permalink**) 04-22-10, 10:02

TABLE keyword might be a trick.

LEFT JOIN TABLE(SELECT ...

archie.by · #3 (**permalink**) 04-22-10, 10:17

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

tonkuma · #4 (**permalink**) 04-22-10, 10:33

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
;

archie.by · #5 (**permalink**) 04-22-10, 10:56

Thanks tonkuma

Lenny77 · #6 (**permalink**) 04-22-10, 11:17

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

stolze · #7 (**permalink**) 04-22-10, 13:33

How did you come up with the 10x slower if you original query had a syntax problem? What are you comparing?

archie.by · #8 (**permalink**) 04-23-10, 04:36

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.

archie.by · #9 (**permalink**) 04-23-10, 04:54

Thanks for your solution Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech