Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FULL OUTER JOIN 2

Status
Not open for further replies.
cristi22

cristi22

MIS
Aug 17, 2005
155
US
Hello all -

I have to merge the following two statements into one select to produce 8 columns of data (q1 thru q4 nrfp, q1 thru q4 nrf2p) based on the ssn number.

I need to use the FULL OUTER JOIN and create one record with NRFP & NRF2P columns.

Any ideas are appreciated!!!!

--nrfp
Code: 
select
pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
,pnrfp.MEMBER_KEY
, fnrfp.FUND 
, pnrfp.EMPLOYER_NO
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN 	fnrfp.QUANTITY END) AS Q4_NRFP_EARN
, sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
 FROM
	MEMBER_WORK_PERIOD_FUND fnrfp,
	MEMBER_WORK_PERIOD pnrfp
WHERE
	fnrfp.MVPOS=pnrfp.MVPOS
	AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
	AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
	AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
GROUP BY
	pnrfp.EMPLOYER_NO
	,pnrfp.MEMBER_KEY
	,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
	, fnrfp.FUND

----------nrf2p-----------------------------
Code: 
select
	pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
,pNRF2P.MEMBER_KEY
, fNRF2P.FUND 
, pNRF2P.EMPLOYER_NO
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END) AS Q1_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END) AS Q2_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END) AS Q3_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN 	fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
, sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04
 	
FROM

	MEMBER_WORK_PERIOD_FUND fNRF2P,
	MEMBER_WORK_PERIOD pNRF2P
	
WHERE
	fNRF2P.MVPOS=pNRF2P.MVPOS
	AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
	AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
	AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
	AND pNRF2P.MEMBER_KEY IN ('423103219')
GROUP BY
	pNRF2P.EMPLOYER_NO
	,pNRF2P.MEMBER_KEY
	,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
	, fNRF2P.FUND

THANKS!
cristi [bigears]
 
Sort by date Sort by votes
One way would be to UNION the two queries into a temporary table and then have a query against that temporary table.

The first of the inner queries would have literals for the FUND2 values, and the secon for the FUND values.

The outer query can then sum 'the sums' to give the required results:
select emp,fund...
from
(select emp,fund,sum(..),sum(..),sum(..),sum(..),0,0,0,0) from group by ....
UNION
(select emp,fund,0,0,0,0,sum(..),sum(..),sum(..),sum(..).) from group by.... ) as T1
group by
....















 
Upvote 0 Downvote
Thanks Brian!
So it would be:
select fund, employer from
(select..... from NRFp.table)
union
(select..... from NRF2p.table)
where nrfp.ssn = nrf2p.ssn ?????
thanks,
cristi
 
Upvote 0 Downvote
Not quite Cristi, but very close. The query is in three elements, as you have identified. The two inner selects each work as your seperate queries in the first posting, but dummy totals are provided for the alternate query. The first query has zeros for the FUND2 totals, and the second has zeros for the basic FUND totals.

Because the structure of the two inner queries is identical, in the number and format of the columns, DB2 allows you to UNION the two result tables together (effectively concatenating them).

The outer query runs against the combined inner reults table, and groups together all items with the same key value, effectively adding together matching rows from the two queries.

I have put together my final SQL:

select
MemberFund
,Member
,Fund
,Employer
,sum(Q1_NRFP_EARN) as Q1_NRFP_EARN)
,sum(Q2_NRFP_EARN) as Q2_NRFP_EARN)
,sum(Q3_NRFP_EARN) as Q3_NRFP_EARN)
,sum(Q4_NRFP_EARN) as Q4_NRFP_EARN)
,sum(Q1_NRF2P_EARN) as Q1_NRF2P_EARN)
,sum(Q2_NRF2P_EARN) as Q2_NRF2P_EARN)
,sum(Q3_NRF2P_EARN) as Q3_NRF2P_EARN)
,sum(Q4_NRF2P_EARN) as Q4_NRF2P_EARN)
from
(select
pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END as MemberFund
,pnrfp.MEMBER_KEY as Member
,fnrfp.FUND as Fund
,pnrfp.EMPLOYER_NO as Employer

, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503') THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
, SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN fnrfp.QUANTITY END) AS Q4_NRFP_EARN
, sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
, sum(0) AS Q1_NRF2P_EARN
, sum(0) AS Q2_NRF2P_EARN
, sum(0) AS Q3_NRF2P_EARN
, sum(0) AS Q4_NRF2P_EARN
FROM
MEMBER_WORK_PERIOD_FUND fnrfp,
MEMBER_WORK_PERIOD pnrfp
WHERE
fnrfp.MVPOS=pnrfp.MVPOS
AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
AND fnrfp.FUND IN ('NRFP' ) AND fnrfp.APP ='GW'
GROUP BY
pnrfp.EMPLOYER_NO
,pnrfp.MEMBER_KEY
,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
,fnrfp.FUND
UNION
select
pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
,pNRF2P.MEMBER_KEY as Member
, fNRF2P.FUND as Fund
, pNRF2P.EMPLOYER_NO as Employer
, sum(0) AS Q1_NRFP_EARN
, sum(0) AS Q2_NRFP_EARN
, sum(0) AS Q3_NRFP_EARN
, sum(0) AS Q4_NRFP_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503') THEN fNRF2P.QUANTITY END) AS Q1_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END) AS Q2_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END) AS Q3_NRF2P_EARN
, SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
, sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04
FROM
MEMBER_WORK_PERIOD_FUND fNRF2P,
MEMBER_WORK_PERIOD pNRF2P

WHERE
fNRF2P.MVPOS=pNRF2P.MVPOS
AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
AND fNRF2P.FUND IN ('NRF2P' ) AND fNRF2P.APP ='GW'
AND pNRF2P.MEMBER_KEY IN ('423103219')
GROUP BY
pNRF2P.EMPLOYER_NO
,pNRF2P.MEMBER_KEY
,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
,fNRF2P.FUND) as T1
group by
MemberFund
,Member
,Fund
,Employer
;
 
Upvote 0 Downvote
Brain -

Thank you for you time & I really appreciate your help!

I run the command (got rid of the ")" ) & here is the message:

QL0206N "Q1_NRF2P_EARN " is not valid in the context where it is used.

Code: 
select
   MemberFund
  ,Member    
  ,Fund      
  ,Employer
  ,sum(Q1_NRFP_EARN)     as Q1_NRFP_EARN 
  ,sum(Q2_NRFP_EARN)     as Q2_NRFP_EARN  
  ,sum(Q3_NRFP_EARN)     as Q3_NRFP_EARN 
  ,sum(Q4_NRFP_EARN)     as Q4_NRFP_EARN 
  ,sum(Q1_NRF2P_EARN)    as Q1_NRF2P_EARN
  ,sum(Q2_NRF2P_EARN)    as Q2_NRF2P_EARN
  ,sum(Q3_NRF2P_EARN)    as Q3_NRF2P_EARN
  ,sum(Q4_NRF2P_EARN)    as Q4_NRF2P_EARN
from
        (select
         pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END  as MemberFund
        ,pnrfp.MEMBER_KEY                                                  as Member
        ,fnrfp.FUND                                                        as Fund
        ,pnrfp.EMPLOYER_NO                                                 as Employer
        
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN     fnrfp.QUANTITY END) AS Q4_NRFP_EARN
        , sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
        , sum(0)            AS Q1_NRF2P_EARN   
        , sum(0)            AS Q2_NRF2P_EARN  
        , sum(0)            AS Q3_NRF2P_EARN  
        , sum(0)            AS Q4_NRF2P_EARN  
         FROM
            MEMBER_WORK_PERIOD_FUND fnrfp,
            MEMBER_WORK_PERIOD pnrfp
        WHERE
            fnrfp.MVPOS=pnrfp.MVPOS
            AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
            AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
            AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
        GROUP BY
            pnrfp.EMPLOYER_NO
            ,pnrfp.MEMBER_KEY
            ,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
            ,fnrfp.FUND 
UNION
        select
            pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
        ,pNRF2P.MEMBER_KEY                                                      as Member    
        , fNRF2P.FUND                                                           as Fund      
        , pNRF2P.EMPLOYER_NO                                                    as Employer  
        , sum(0)             AS Q1_NRFP_EARN 
        , sum(0)             AS Q2_NRFP_EARN 
        , sum(0)             AS Q3_NRFP_EARN 
        , sum(0)             AS Q4_NRFP_EARN 
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END)                        AS Q1_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q2_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q3_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN     fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
        , sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04     
        FROM
            MEMBER_WORK_PERIOD_FUND fNRF2P,
            MEMBER_WORK_PERIOD pNRF2P
            
        WHERE
            fNRF2P.MVPOS=pNRF2P.MVPOS
            AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
            AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
            AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
            AND pNRF2P.MEMBER_KEY IN ('423103219')
        GROUP BY
            pNRF2P.EMPLOYER_NO
            ,pNRF2P.MEMBER_KEY
            ,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
            ,fNRF2P.FUND)   as T1
group by
     MemberFund     
    ,Member         
    ,Fund           
    ,Employer
;
 
Upvote 0 Downvote
Cristi,

Sorry, but I'm really tied up for a couple of days, so I cannot do much for you immediately. I seem to have missed the following field in the SQLs when introducing the 0 totals. Try putting the extra 0 fields in (one for each subquery), and make sure that the queries each end up with the same columns in the same sequence.

sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04

NB If anyone else has a few minutes, feel welcome to take over, as I am really stuck for time at the moment
 
Upvote 0 Downvote
Thanks Brian -
I am trying different things. Individual SQL works just fine. I think I need to reference each table somehow!
thanks,cristi
 
Upvote 0 Downvote
Cristi,

Have you determine which section of your sql the error has occurred in? I would suggest running the portion of the sub-query before the union and then the portion of the sub-query after the union. If both of those run fine, try running the entire sub-query. That should tell us whether the problem is with the sub-selects themselves, or if the problem is with matching incompatible fields across the union.

- Dan
 
Upvote 0 Downvote
Hello Dan & thank you for your time.

The individual queries run fine (select.... from nrfp, select ... from nrf2p & union it together)

This is the idea, but i think i might need to use temp tables & full outer join

select nrfp.q1,nrf2p.q1 from nrnp full outer join nrf2p on
nrfp.ssn=nrf2p.ssn

thanks for all your help!
 
Upvote 0 Downvote
Cristi,
DB2 does support a full outer join, although it is a little tricky to work with. The safest thing is to full outer join your original sub-queries from your first post.

Code: 
select <field list>
from ( <sub-query1> ) t1
  full outer join ( <sub-query2> ) t2
  on t1.ssn = t2.ssn
order by <field-list>

All WHERE conditions should be handled within the sub-queries. For more details on the full outer join, take a look at DB2 UDB SQL Cookbookby Graeme Birchall.
 
Upvote 0 Downvote
thanks all for your help!
One of the solutions is to create 2 temp tables & full outer join them on the ssn in the procedure, here is the code...& it's not working :(
Code: 
select
   MemberFund
  ,Member    
  ,Fund      
  ,Employer
  ,sum(Q1_NRFP_EARN)     as Q1_NRFP_EARN 
  ,sum(Q2_NRFP_EARN)     as Q2_NRFP_EARN  
  ,sum(Q3_NRFP_EARN)     as Q3_NRFP_EARN 
  ,sum(Q4_NRFP_EARN)     as Q4_NRFP_EARN 
  ,sum(Q1_NRF2P_EARN)    as Q1_NRF2P_EARN
  ,sum(Q2_NRF2P_EARN)    as Q2_NRF2P_EARN
  ,sum(Q3_NRF2P_EARN)    as Q3_NRF2P_EARN
  ,sum(Q4_NRF2P_EARN)    as Q4_NRF2P_EARN
from
        (select
         pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END  as MemberFund
        ,pnrfp.MEMBER_KEY                                                  as Member
        ,fnrfp.FUND                                                        as Fund
        ,pnrfp.EMPLOYER_NO                                                 as Employer
        
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN     fnrfp.QUANTITY END) AS Q4_NRFP_EARN
        , sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
        , sum(0)            AS Q1_NRF2P_EARN   
        , sum(0)            AS Q2_NRF2P_EARN  
        , sum(0)            AS Q3_NRF2P_EARN  
        , sum(0)            AS Q4_NRF2P_EARN  
         FROM
            MEMBER_WORK_PERIOD_FUND fnrfp,
            MEMBER_WORK_PERIOD pnrfp
        WHERE
            fnrfp.MVPOS=pnrfp.MVPOS
            AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
            AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
            AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
            AND pNRFP.MEMBER_KEY IN ('423103219')
        GROUP BY
            pnrfp.EMPLOYER_NO
            ,pnrfp.MEMBER_KEY
            ,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
            ,fnrfp.FUND ) t1
full outer join (
        select
            pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
        ,pNRF2P.MEMBER_KEY                                                      as Member    
        , fNRF2P.FUND                                                           as Fund      
        , pNRF2P.EMPLOYER_NO                                                    as Employer  
        , sum(0)             AS Q1_NRFP_EARN 
        , sum(0)             AS Q2_NRFP_EARN 
        , sum(0)             AS Q3_NRFP_EARN 
        , sum(0)             AS Q4_NRFP_EARN 
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END)                        AS Q1_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q2_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q3_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN     fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
        , sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04     
        FROM
            MEMBER_WORK_PERIOD_FUND fNRF2P,
            MEMBER_WORK_PERIOD pNRF2P
            
        WHERE
            fNRF2P.MVPOS=pNRF2P.MVPOS
            AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
            AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
            AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
            AND pNRF2P.MEMBER_KEY IN ('423103219')
        GROUP BY
            pNRF2P.EMPLOYER_NO
            ,pNRF2P.MEMBER_KEY
            ,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
            ,fNRF2P.FUND)   as T2
on t1.MEMBER_KEY =t2.MEMBER_KEY  ???????
group by
     MemberFund     
    ,Member         
    ,Fund           
    ,Employer
 
Upvote 0 Downvote
You have to refer member_key by the alias names you provided.

Code: 
on t1.MEMBER = t2.MEMBER
 
Upvote 0 Downvote
Thanks ddiamond, i've used the alias names

"T1.MEMBER_KEY" is not valid in the context where it is used.
SQLSTATE=42703

SQL0206N "T1.MEMBER_KEY " is not valid in the context where it is used.
[ponder]
 
Upvote 0 Downvote
oops sorry i've changed the top of the query:
Code: 
select
   MemberFund
  ,Member_key   
  ,Fund      
  ,Employer
  ,sum(Q1_NRFP_EARN)     as Q1_NRFP_EARN 
  ,sum(Q2_NRFP_EARN)     as Q2_NRFP_EARN  
  ,sum(Q3_NRFP_EARN)     as Q3_NRFP_EARN 
  ,sum(Q4_NRFP_EARN)     as Q4_NRFP_EARN 
  ,sum(Q1_NRF2P_EARN)    as Q1_NRF2P_EARN
  ,sum(Q2_NRF2P_EARN)    as Q2_NRF2P_EARN
  ,sum(Q3_NRF2P_EARN)    as Q3_NRF2P_EARN
  ,sum(Q4_NRF2P_EARN)    as Q4_NRF2P_EARN
from
        (select
         pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END  as MemberFund
        ,pnrfp.MEMBER_KEY                                                  as Member
        ,fnrfp.FUND                                                        as Fund
        ,pnrfp.EMPLOYER_NO                                                 as Employer
        
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN     fnrfp.QUANTITY END) AS Q4_NRFP_EARN
        , sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
        , sum(0)            AS Q1_NRF2P_EARN   
        , sum(0)            AS Q2_NRF2P_EARN  
        , sum(0)            AS Q3_NRF2P_EARN  
        , sum(0)            AS Q4_NRF2P_EARN  
         FROM
            MEMBER_WORK_PERIOD_FUND fnrfp,
            MEMBER_WORK_PERIOD pnrfp
        WHERE
            fnrfp.MVPOS=pnrfp.MVPOS
            AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
            AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
            AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
            AND pNRFP.MEMBER_KEY IN ('423103219')
        GROUP BY
            pnrfp.EMPLOYER_NO
            ,pnrfp.MEMBER_KEY
            ,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
            ,fnrfp.FUND ) 
--t1
group by
     MemberFund     
    ,Member         
    ,Fund           
    ,Employer

full outer join (
        select
            pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
        ,pNRF2P.MEMBER_KEY                                                      as Member    
        , fNRF2P.FUND                                                           as Fund      
        , pNRF2P.EMPLOYER_NO                                                    as Employer  
        , sum(0)             AS Q1_NRFP_EARN 
        , sum(0)             AS Q2_NRFP_EARN 
        , sum(0)             AS Q3_NRFP_EARN 
        , sum(0)             AS Q4_NRFP_EARN 
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END)                        AS Q1_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q2_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q3_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN     fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
        , sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04     
        FROM
            MEMBER_WORK_PERIOD_FUND fNRF2P,
            MEMBER_WORK_PERIOD pNRF2P
            
        WHERE
            fNRF2P.MVPOS=pNRF2P.MVPOS
            AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
            AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
            AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
            AND pNRF2P.MEMBER_KEY IN ('423103219')
        GROUP BY
            pNRF2P.EMPLOYER_NO
            ,pNRF2P.MEMBER_KEY
            ,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
            ,fNRF2P.FUND)   as T2
on t1.MEMBER_key  =t2.MEMBER_key
group by
     MemberFund     
    ,Member_key         
    ,Fund           
    ,Employer

error:[red]
SQL0104N An unexpected token "Employer" was found following "Fund ,Member ,
Fund ,". Expected tokens may include: "(". SQLSTATE=42601[/red]
 
Upvote 0 Downvote
i am sorry, the above code is not correct (i had extract group in the st)


here is my code:
Code: 
select
   MemberFund
  ,Member_key   
  ,Fund      
  ,Employer
  ,sum(Q1_NRFP_EARN)     as Q1_NRFP_EARN 
  ,sum(Q2_NRFP_EARN)     as Q2_NRFP_EARN  
  ,sum(Q3_NRFP_EARN)     as Q3_NRFP_EARN 
  ,sum(Q4_NRFP_EARN)     as Q4_NRFP_EARN 
  ,sum(Q1_NRF2P_EARN)    as Q1_NRF2P_EARN
  ,sum(Q2_NRF2P_EARN)    as Q2_NRF2P_EARN
  ,sum(Q3_NRF2P_EARN)    as Q3_NRF2P_EARN
  ,sum(Q4_NRF2P_EARN)    as Q4_NRF2P_EARN
from
        (select
         pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END  as MemberFund
        ,pnrfp.MEMBER_KEY                                                  as Member
        ,fnrfp.FUND                                                        as Fund
        ,pnrfp.EMPLOYER_NO                                                 as Employer
        
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN     fnrfp.QUANTITY END) AS Q4_NRFP_EARN
        , sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
        , sum(0)            AS Q1_NRF2P_EARN   
        , sum(0)            AS Q2_NRF2P_EARN  
        , sum(0)            AS Q3_NRF2P_EARN  
        , sum(0)            AS Q4_NRF2P_EARN  
         FROM
            MEMBER_WORK_PERIOD_FUND fnrfp,
            MEMBER_WORK_PERIOD pnrfp
        WHERE
            fnrfp.MVPOS=pnrfp.MVPOS
            AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
            AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
            AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
            AND pNRFP.MEMBER_KEY IN ('423103219')
        GROUP BY
            pnrfp.EMPLOYER_NO
            ,pnrfp.MEMBER_KEY
            ,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
            ,fnrfp.FUND ) t1
full outer join (
        select
            pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
        ,pNRF2P.MEMBER_KEY                                                      as Member    
        , fNRF2P.FUND                                                           as Fund      
        , pNRF2P.EMPLOYER_NO                                                    as Employer  
        , sum(0)             AS Q1_NRFP_EARN 
        , sum(0)             AS Q2_NRFP_EARN 
        , sum(0)             AS Q3_NRFP_EARN 
        , sum(0)             AS Q4_NRFP_EARN 
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END)                        AS Q1_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q2_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q3_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN     fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
        , sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04     
        FROM
            MEMBER_WORK_PERIOD_FUND fNRF2P,
            MEMBER_WORK_PERIOD pNRF2P
            
        WHERE
            fNRF2P.MVPOS=pNRF2P.MVPOS
            AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
            AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
            AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
            AND pNRF2P.MEMBER_KEY IN ('423103219')
        GROUP BY
            pNRF2P.EMPLOYER_NO
            ,pNRF2P.MEMBER_KEY
            ,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
            ,fNRF2P.FUND)   T2

on t1.MEMBER_key  =t2.MEMBER_key
	
group by
  MemberFund     
    ,Member_key        
    ,Fund           
,Employer

T1.MEMBER_KEY" is not valid in the context where it is used.
SQLSTATE=42703

Please help! [3eyes]
 
Upvote 0 Downvote
Cristi,

Out of curiousity, what happens if you remove the 't1.' from the 'on t1.MEMBER_key =t2.MEMBER_key' bit?

Marc
 
Upvote 0 Downvote
Your line:
[red]on t1.MEMBER_key =t2.MEMBER_key[/red]

is incorrect. Because your sub-queries use the alias MEMBER for MEMBER_KEY, you need the following instead:

[blue]on t1.MEMBER = t2.MEMBER[/blue]

I found a few other syntax problems which I've fixed. I've highligted all of my modification in blue. For example, in your main select you must explicitly qualify MemberFund with T1 or T2. That is because unlike a union, a full outer join will return all of the fields from both sub-queries side by side. For this reason, I've also removed the sum(0) lines. They were needed for your original union solution, but not with this one.

Code: 
select
   [blue]coalesce(t1.MemberFund,t2.memberfund) as memberfund
  ,coalesce(t1.Member,t2.Member) as member   
  ,coalesce(t1.Fund,t2.Fund) as Fund      
  ,coalesce(t1.Employer, t2.Employer) as Employer[/blue]
  ,sum(Q1_NRFP_EARN)     as Q1_NRFP_EARN 
  ,sum(Q2_NRFP_EARN)     as Q2_NRFP_EARN  
  ,sum(Q3_NRFP_EARN)     as Q3_NRFP_EARN 
  ,sum(Q4_NRFP_EARN)     as Q4_NRFP_EARN 
  ,sum(Q1_NRF2P_EARN)    as Q1_NRF2P_EARN
  ,sum(Q2_NRF2P_EARN)    as Q2_NRF2P_EARN
  ,sum(Q3_NRF2P_EARN)    as Q3_NRF2P_EARN
  ,sum(Q4_NRF2P_EARN)    as Q4_NRF2P_EARN
from
        (select
         pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END  as MemberFund
        ,pnrfp.MEMBER_KEY                                                  as Member
        ,fnrfp.FUND                                                        as Fund
        ,pnrfp.EMPLOYER_NO                                                 as Employer
        
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200501','200502','200503')  THEN fnrfp.QUANTITY END) AS Q1_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200504','200505','200506') and fnrfP.app='GW' THEN fnrfp.QUANTITY END) AS Q2_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200507','200508','200509') and fnrfP.app='GW' THEN fnrfP.QUANTITY END) AS Q3_NRFP_EARN
        , SUM ( CASE WHEN pnrfp.WORK_PERIOD IN ( '200510','200511','200512') and fNRFP.app='GW' THEN     fnrfp.QUANTITY END) AS Q4_NRFP_EARN
        , sum (case when fnrfp.app ='GW' then fnrfp.QUANTITY end ) as earn_NRFP_04
         FROM
            MEMBER_WORK_PERIOD_FUND fnrfp,
            MEMBER_WORK_PERIOD pnrfp
        WHERE
            fnrfp.MVPOS=pnrfp.MVPOS
            AND fnrfp.MEMBER_KEY=pnrfp.MEMBER_KEY
            AND LEFT(pnrfp.WORK_PERIOD,4)='2005'
            AND  fnrfp.FUND IN ('NRFP' ) AND  fnrfp.APP ='GW'
            AND pNRFP.MEMBER_KEY IN ('423103219')
        GROUP BY
            pnrfp.EMPLOYER_NO
            ,pnrfp.MEMBER_KEY
            ,pnrfp.MEMBER_KEY || CASE fnrfp.APP WHEN 'GW' THEN fnrfp.FUND END
            ,fnrfp.FUND ) t1
full outer join (
        select
            pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END as MemberFund
        ,pNRF2P.MEMBER_KEY                                                      as Member    
        , fNRF2P.FUND                                                           as Fund      
        , pNRF2P.EMPLOYER_NO                                                    as Employer  
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200501','200502','200503')  THEN fNRF2P.QUANTITY END)                        AS Q1_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200504','200505','200506') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q2_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200507','200508','200509') and fNRF2P.app='GW' THEN fNRF2P.QUANTITY END)     AS Q3_NRF2P_EARN
        , SUM ( CASE WHEN pNRF2P.WORK_PERIOD IN ( '200510','200511','200512') and fNRF2P.app='GW' THEN     fNRF2P.QUANTITY END) AS Q4_NRF2P_EARN
        , sum (case when fNRF2P.app ='GW' then fNRF2P.QUANTITY end ) as earn_NRF2P_04     
        FROM
            MEMBER_WORK_PERIOD_FUND fNRF2P,
            MEMBER_WORK_PERIOD pNRF2P
            
        WHERE
            fNRF2P.MVPOS=pNRF2P.MVPOS
            AND fNRF2P.MEMBER_KEY=pNRF2P.MEMBER_KEY
            AND LEFT(pNRF2P.WORK_PERIOD,4)='2005'
            AND  fNRF2P.FUND IN ('NRF2P' ) AND  fNRF2P.APP ='GW'
            AND pNRF2P.MEMBER_KEY IN ('423103219')
        GROUP BY
            pNRF2P.EMPLOYER_NO
            ,pNRF2P.MEMBER_KEY
            ,pNRF2P.MEMBER_KEY || CASE fNRF2P.APP WHEN 'GW' THEN fNRF2P.FUND END
            ,fNRF2P.FUND)   T2

[blue]on t1.MEMBER  =t2.MEMBER[/blue]
    
group by
   [blue]coalesce(t1.MemberFund,t2.memberfund)
  ,coalesce(t1.Member,t2.Member)
  ,coalesce(t1.Fund,t2.Fund)
  ,coalesce(t1.Employer, t2.Employer)[/blue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

P
  • Locked
  • Question
DB2 LUW SYSCATSPACE is full
Replies
0
Views
572
prashant_0701
P
MichaelDe
  • Locked
  • Question
SQL Update with File Join
Replies
10
Views
2K
MichaelDe
MichaelDe
evr72
  • Locked
  • Question
Update field from one table with a join table
Replies
1
Views
360
evr72
evr72
eaglei22
  • Locked
  • Question
DB2 Update coolumn based off of results from join?
Replies
1
Views
242
eaglei22
eaglei22
czab
  • Locked
  • Question
Problem with left outer join 2
Replies
7
Views
181
czab
czab

Part and Inventory Search

Sponsor

Back
Top