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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HOW TO SELECT MULTIPLE COLUMNS instead OF ROWS 3

Status
Not open for further replies.
cristi22

cristi22

MIS
Aug 17, 2005
155
US
Hello All!

The select statement creates the following:
Code: 
KEY      EMPL  	 EARN	WORK_PERIOD APPFUND
99999999 0010231 3025.83 200503	   GW	HDCONP
99999999 0010231         200506	   GW	HDCONP
99999999 0010231  433.5  200503	   HW	NHHW
99999999 0010231         200506	   HW	NHHW

How do the select to display:

Code: 
KEY         EMPLO	EARN  HW	WORK_PERIOD FUND
9999 0010231	3025.83  433.5 200503	  HDCONP

Here is my SELECT:
Code: 
SELECT 
 MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO


, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200501','200502','200503') THEN 
MEMBER_WORK_PERIOD_FUND.QUANTITY END) AS EARN


--, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200504','200505','200506') THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q2_QTY_2005
--, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200507','200508','200509') THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q3_QTY_2005
--, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200510','200511','200512') THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q4_QTY_2005

, MEMBER_WORK_PERIOD.WORK_PERIOD
, MEMBER_WORK_PERIOD_FUND.APP      
--, MEMBER_WORK_PERIOD_FUND.FUND   

FROM   
	MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE

	MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
	AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
	AND  MEMBER_WORK_PERIOD_FUND.FUND IN ( 'HDCONP','NHHW') AND  MEMBER_WORK_PERIOD_FUND.APP IN ('GW','HW')

	--AND MEMBER_WORK_PERIOD_FUND.MVPOS= MEMBER_WORK_PERIOD_FUND.SVPOS
	
	AND LEFT(MEMBER_WORK_PERIOD.WORK_PERIOD,4)='2005'
	 AND MEMBER_WORK_PERIOD.MEMBER_KEY='045094654'
	
GROUP BY
	MEMBER_WORK_PERIOD.EMPLOYER_NO 	
	,MEMBER_WORK_PERIOD.MEMBER_KEY
	,MEMBER_WORK_PERIOD_FUND.APP


     
	,MEMBER_WORK_PERIOD_FUND.FUND   
	,MEMBER_WORK_PERIOD.WORK_PERIOD

thanks
cristi[3eyes]
 
Sort by date Sort by votes
Cristi,
Are you looking to display the first row?
If so, you can try adding "FETCH FIRST ROW ONLY" to return one row. What version of DB2 are you using?
-PK
 
Upvote 0 Downvote
Cristi,

I think you will need to use a CASE statement on the column that contains the values 'GW' and 'HW' combined with SUM. Strangely enough we were discussing this in a thread yesterday. Have a look at thread178-1183822 for an example of CASE and SUM.

Marc
 
Upvote 0 Downvote
Cristi,
Having just read back thru your SQL, I see that you have got a perfect example of SUM/CASE. Sorry.

Try something like this instead:
Code: 
SELECT 
 MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO


, SUM ( CASE WHEN MEMBER_WORK_PERIOD_FUND.APP = 'GW' THEN MEMBER_WORK_PERIOD_FUND.QUANTITY  ELSE 0 END) AS EARN

, SUM ( CASE WHEN MEMBER_WORK_PERIOD_FUND.APP = 'HW' THEN MEMBER_WORK_PERIOD_FUND.QUANTITY  ELSE 0 END) AS HW


, MEMBER_WORK_PERIOD.WORK_PERIOD
, MEMBER_WORK_PERIOD_FUND.APP      

FROM   
    MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE

    MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  MEMBER_WORK_PERIOD_FUND.FUND IN ( 'HDCONP','NHHW') 
    AND  MEMBER_WORK_PERIOD_FUND.APP IN ('GW','HW')
    AND  MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200501','200502','200503')
    --AND MEMBER_WORK_PERIOD_FUND.MVPOS= MEMBER_WORK_PERIOD_FUND.SVPOS
    
    AND LEFT(MEMBER_WORK_PERIOD.WORK_PERIOD,4)='2005'
     AND MEMBER_WORK_PERIOD.MEMBER_KEY='045094654'
    
GROUP BY
    MEMBER_WORK_PERIOD.EMPLOYER_NO     
    ,MEMBER_WORK_PERIOD.MEMBER_KEY
    ,MEMBER_WORK_PERIOD_FUND.APP
 
Upvote 0 Downvote
thanks Marc, your code still returns two rows
And it got a bit more complex....[ponder]

Now I need to extract all records with FUND = HDCONP and APP = GW or FUND LIKE 'NHHW' and APP = 'HW'
I also would like to generate the flexible code, so it could be used for other fund/app criteria. I also need to add the FUND if the APP is gw.

Here is my code & it only works for EARN
Code: 
SELECT 
 MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO

--Q1
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200501','200502','200503') and app='GW' 
	THEN 	MEMBER_WORK_PERIOD_FUND.QUANTITY END) AS Q1_EARN
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200501','200502','200503') and app='HW' 
	THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END) AS Q1_HRS
--Q2
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200504','200505','200506') and app='GW'
	THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q2_EARN
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200504','200505','200506') and app='HW'
	THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q2_HRS

--Q3
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200507','200508','200509') AND app='GW'
	THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q3_EARN
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200507','200508','200509') AND app='HW'
	THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q3_HRS
--Q4

, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200510','200511','200512') AND app='GW'
	 THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q4_EARN
, SUM ( CASE WHEN MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200510','200511','200512') AND app='HW'
	 THEN MEMBER_WORK_PERIOD_FUND.QUANTITY END ) AS Q4_HRS


FROM   
    MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE

    MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  MEMBER_WORK_PERIOD_FUND.FUND IN ( 'HDCONP','NHHW') AND  MEMBER_WORK_PERIOD_FUND.APP IN ('GW','HW')

  
    AND LEFT(MEMBER_WORK_PERIOD.WORK_PERIOD,4)='2005'
    -- AND MEMBER_WORK_PERIOD.MEMBER_KEY='339768253'
    
GROUP BY
    MEMBER_WORK_PERIOD.EMPLOYER_NO     
    ,MEMBER_WORK_PERIOD.MEMBER_KEY
thanks all for your help!
cristi
 
Upvote 0 Downvote
Cristi,
You appear to contradict yourself slightly as you said that you want (FUND = HDCONP and APP = GW) but then you say that you wish to add (include?) the fund if the APP is GW. Which one should it be?

I'm surprised that the code I gave you produced two rows, as I would have thought that this should be handled by the GROUP BY clause. Could you run the code again, but change the two SUM/CASE statements to just be selects of the Quantity column (you'll need to comment out the group by too) and post the results here.

Marc
 
Upvote 0 Downvote
Marc,

Doesn't the code you provided contain MEMBER_WORK_PERIOD_FUND.APP in the group by? This would produce 2 rows because APP has 2 different values.

- Dan
 
Upvote 0 Downvote
Cristi,

What logic do you want to use for the fund field? In your sample output, you list the fund as HDCONP, but you pulled your data from both HDCONP and NHHW.

- Dan
 
Upvote 0 Downvote
Thanks Dan, i got rid of the app
Here is my new code. I am missing some stuff, the results are not correct, but the idea is

Code: 
SELECT 
	ep.MEMBER_KEY 
	, ep.EMPLOYER_NO
	, CASE ef.APP WHEN 'GW' THEN ef.FUND END
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200501','200502','200503') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q1_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200501','200502','200503') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q1_HRS

FROM   
	MEMBER_WORK_PERIOD_FUND ef, 
	MEMBER_WORK_PERIOD ep,

	MEMBER_WORK_PERIOD_FUND hf, 
	MEMBER_WORK_PERIOD hp

WHERE
	ef.MVPOS=ep.MVPOS  
	AND ep.MEMBER_KEY=ef.MEMBER_KEY 
	AND  ef.FUND = 'HDCONP'AND  ef.APP ='GW'
  
	AND LEFT(ep.WORK_PERIOD,4)='2005'
	--AND ep.MEMBER_KEY='339768253'

AND
	hf.MVPOS=hp.MVPOS  
	AND hp.MEMBER_KEY=hf.MEMBER_KEY 
	AND  hf.FUND = 'NHHW' AND  hf.APP ='HW'
	AND LEFT(hp.WORK_PERIOD,4)='2005'
	AND hp.MEMBER_KEY='339768253'
and
	ef.MEMBER_KEY=hf.MEMBER_KEY
	and ep.MEMBER_KEY=hp.MEMBER_KEY
GROUP BY
    ep.EMPLOYER_NO     
    ,ep.MEMBER_KEY
,CASE ef.APP WHEN 'GW' THEN ef.FUND END

thanks all cristi!

 
Upvote 0 Downvote
Cristi,

The "CASE ef.APP WHEN 'GW' Then ef.FUND END" is probably not necessary because in your where clause you have ef.APP ='GW', so ef.APP will always equal 'GW'. This shouldn't be hurting anything however. I think you may need specify a join between ef and hf. Also need to set the relationship between ep and hp. Otherwise your sums may be duplicating.

I think it will be much easier if you stick to your original approach of joining only 2 tables and using grouping and sums to derive your various columns.

What results do you get from the following
Code: 
SELECT 
 MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO


, SUM ( CASE WHEN MEMBER_WORK_PERIOD_FUND.APP = 'GW' THEN MEMBER_WORK_PERIOD_FUND.QUANTITY  ELSE 0 END) AS EARN

, SUM ( CASE WHEN MEMBER_WORK_PERIOD_FUND.APP = 'HW' THEN MEMBER_WORK_PERIOD_FUND.QUANTITY  ELSE 0 END) AS HW


, MEMBER_WORK_PERIOD.WORK_PERIOD

FROM   
    MEMBER_WORK_PERIOD_FUND, MEMBER_WORK_PERIOD
WHERE

    MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS  
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  MEMBER_WORK_PERIOD_FUND.FUND IN ( 'HDCONP','NHHW') 
    AND  MEMBER_WORK_PERIOD_FUND.APP IN ('GW','HW')
    AND  MEMBER_WORK_PERIOD.WORK_PERIOD IN ( '200501','200502','200503')
    --AND MEMBER_WORK_PERIOD_FUND.MVPOS= MEMBER_WORK_PERIOD_FUND.SVPOS
    
    AND LEFT(MEMBER_WORK_PERIOD.WORK_PERIOD,4)='2005'
     AND MEMBER_WORK_PERIOD.MEMBER_KEY='045094654'
    
GROUP BY
    MEMBER_WORK_PERIOD.EMPLOYER_NO     
    ,MEMBER_WORK_PERIOD.MEMBER_KEY
    ,MEMBER_WORK_PERIOD.WORK_PERIOD
 
Upvote 0 Downvote
Thanks all for your help!
Here is the code & it seemed to work...any suggestions on how to make it better are appreciated!
Code: 
SELECT 
	ep.MEMBER_KEY 
	, ep.EMPLOYER_NO
	, CASE ef.APP WHEN 'GW' THEN ef.FUND END

	--Q1 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200501','200502','200503') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q1_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200501','200502','200503') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q1_HRS
	--Q2
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200504','200505','200506') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q2_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200504','200505','200506') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q2_HRS

	--Q3 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200507','200508','200509') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q3_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200507','200508','200509') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q3_HRS

	--Q4
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200510','200511','200512') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q4_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN( '200510','200511','200512') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q4_HRS

FROM   
	MEMBER_WORK_PERIOD_FUND ef, 
	MEMBER_WORK_PERIOD ep,

	MEMBER_WORK_PERIOD_FUND hf, 
	MEMBER_WORK_PERIOD hp

WHERE
	ef.MVPOS=ep.MVPOS  
	AND ep.MEMBER_KEY=ef.MEMBER_KEY 
	AND  ef.FUND = 'HDCONP'AND  ef.APP ='GW'
  
	AND LEFT(ep.WORK_PERIOD,4)='2005'
	--AND ep.MEMBER_KEY='339768253'

AND
	hf.MVPOS=hp.MVPOS  
	AND hp.MEMBER_KEY=hf.MEMBER_KEY 
	AND  hf.FUND = 'NHHW' AND  hf.APP ='HW'
	AND LEFT(hp.WORK_PERIOD,4)='2005'
	--AND hp.MEMBER_KEY='339768253'
and
	hf.MVPOS=ep.MVPOS  
	AND hp.MEMBER_KEY=ef.MEMBER_KEY 
	AND hf.FUND = 'NHHW' AND  hf.APP ='HW'
	AND hp.WORK_PERIOD=ep.WORK_PERIOD
	AND hp.MEMBER_KEY=ep.MEMBER_KEY

    AND ef.MEMBER_KEY NOT IN (SELECT ssn from VALUATIONS_ALL_RETIREES_2005)
    AND ef.MEMBER_KEY NOT IN (SELECT SSN FROM TEMP_MEM_STS_DE)  

GROUP BY
    ep.EMPLOYER_NO     
    ,ep.MEMBER_KEY
,CASE ef.APP WHEN 'GW' THEN ef.FUND END
 
Upvote 0 Downvote
I was going to use this select to create the MQT & merge it with another table. Can I do it with 'WITH' exp or anything else?
It would be more dynamic than MQT in my case

[ponytails]

I need to add the member's name & dob & other data

with member
select (first select goes in here.....) ,

select name,dob from member.......

any ideas ????
thanks,
cristi!
 
Upvote 0 Downvote
Since member's name and dob are constant for a given member, you can simply add those fields to your select clause and group by clause. I don't see why you would need a with clause. What table are these fields coming from?
 
Upvote 0 Downvote
thanks ddiamond! I could do that, but it takes forever
i'll try to set-up MQT
 
Upvote 0 Downvote
Can I use union in MQT?

the following statement returns:
SQL0153N The statement does not include a required column list.

Any ideas?
thanks a lot, cristi

Code: 
CREATE TABLE VALS_UNRF_GR1 AS 
(
-- HD2HP & HDCONP ---------------------------------
---------------------------------------------------
SELECT 
	ep.MEMBER_KEY 
	, ep.EMPLOYER_NO
	, CASE ef.APP WHEN 'GW' THEN ef.FUND END

	--Q1 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200501','200502','200503') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q1_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200501','200502','200503') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q1_HRS
	--Q2
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200504','200505','200506') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q2_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200504','200505','200506') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q2_HRS

	--Q3 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200507','200508','200509') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q3_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200507','200508','200509') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q3_HRS

	--Q4
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200510','200511','200512') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q4_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN( '200510','200511','200512') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q4_HRS

FROM   
	MEMBER_WORK_PERIOD_FUND ef, 
	MEMBER_WORK_PERIOD ep,

	MEMBER_WORK_PERIOD_FUND hf, 
	MEMBER_WORK_PERIOD hp

WHERE
	ef.MVPOS=ep.MVPOS  
	AND ep.MEMBER_KEY=ef.MEMBER_KEY 
	AND LEFT(ep.WORK_PERIOD,4)='2005'
	AND  ef.FUND IN ('HDCONP', 'HD2HP' ) AND  ef.APP ='GW'
AND
	hf.MVPOS=hp.MVPOS  
	AND hp.MEMBER_KEY=hf.MEMBER_KEY 
	AND LEFT(hp.WORK_PERIOD,4)='2005'
	AND  hf.FUND IN  ('NHHW','NHH2W','NHH3W','NHH4W','CTHW') AND  hf.APP ='HW'
and
	hf.MVPOS=ep.MVPOS  
	AND hp.MEMBER_KEY=ef.MEMBER_KEY 
	AND hp.WORK_PERIOD=ep.WORK_PERIOD
	AND hp.MEMBER_KEY=ep.MEMBER_KEY

	AND ef.MEMBER_KEY NOT IN (SELECT ssn from VALUATIONS_ALL_RETIREES_2005)
	AND ef.MEMBER_KEY NOT IN (SELECT SSN FROM TEMP_MEM_STS_DE)  

	AND ep.MEMBER_KEY='003281814'
GROUP BY
    ep.EMPLOYER_NO     
    ,ep.MEMBER_KEY
    ,CASE ef.APP WHEN 'GW' THEN ef.FUND END

UNION
-----------------------------
--- COTTON
-----------------------------
SELECT 
	ep.MEMBER_KEY 
	, ep.EMPLOYER_NO
	, CASE ef.APP WHEN 'GW' THEN ef.FUND END

	--Q1 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200501','200502','200503') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q1_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200501','200502','200503') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q1_HRS
	--Q2
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200504','200505','200506') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q2_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200504','200505','200506') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q2_HRS

	--Q3 
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200507','200508','200509') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q3_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN ( '200507','200508','200509') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q3_HRS

	--Q4
	, SUM ( CASE WHEN ep.WORK_PERIOD IN ( '200510','200511','200512') and ef.app='GW' 
		THEN 	ef.QUANTITY END) AS Q4_EARN

	, SUM ( CASE WHEN hp.WORK_PERIOD IN( '200510','200511','200512') and hf.app='HW' 
		THEN 	hf.QUANTITY END) AS Q4_HRS

FROM   
	MEMBER_WORK_PERIOD_FUND ef, 
	MEMBER_WORK_PERIOD ep,

	MEMBER_WORK_PERIOD_FUND hf, 
	MEMBER_WORK_PERIOD hp

WHERE
	ef.MVPOS=ep.MVPOS  
	AND ep.MEMBER_KEY=ef.MEMBER_KEY 
	AND LEFT(ep.WORK_PERIOD,4)='2005'


 	AND  ef.FUND = 'CTP'  AND  ef.APP ='GW'

	AND ep.MEMBER_KEY='003281814'

AND
	hf.MVPOS=hp.MVPOS  
	AND hp.MEMBER_KEY=hf.MEMBER_KEY 
	AND LEFT(hp.WORK_PERIOD,4)='2005'

	AND hf.FUND = 'CTHW' AND  hf.APP ='HW'

and
	hf.MVPOS=ep.MVPOS  
	AND hp.MEMBER_KEY=ef.MEMBER_KEY 
	AND hp.WORK_PERIOD=ep.WORK_PERIOD
	AND hp.MEMBER_KEY=ep.MEMBER_KEY

 	AND ef.MEMBER_KEY NOT IN (SELECT ssn from VALUATIONS_ALL_RETIREES_2005)
	AND ef.MEMBER_KEY NOT IN (SELECT SSN FROM TEMP_MEM_STS_DE)  

GROUP BY
    ep.EMPLOYER_NO     
    ,ep.MEMBER_KEY
,CASE ef.APP WHEN 'GW' THEN ef.FUND END


 ) DATA INITIALLY DEFERRED REFRESH DEFERRED

 
Upvote 0 Downvote
Cristi,
Do you get any errors if you run the select portion by itself?

I tested your create syntax with my own select union query and it worked fine. So I can only guess the problem is with your select, not your create table statement.

For example - the following worked fine for me:
Code: 
create table geniusdata.test as (
select * from geniusdata.cm_claim_master
union
select * from geniusdata.cm_claim_master
) DATA INITIALLY DEFERRED REFRESH DEFERRED
;
 
Upvote 0 Downvote
Cristi,

What is MQT? I ran the above query using the command line editor.

- Dan
 
Upvote 0 Downvote
Dan -
MQT = Materialized Query Tables

A materialized query table contains the results of a query. The DB2 optimizer knows this and
can, if appropriate, redirect a query that is against the source table(s) to use the materialized
query table instead. This can make the query run much faster.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question
STRDBG not bringing up options to set breakpoints
Replies
10
Views
283
chuckw0325
C
tekpr00
  • Locked
  • Question
Adding storage to DB2 PureScale Cluster
Replies
0
Views
305
tekpr00
tekpr00
gdg1976
  • Locked
  • Question
extract rows with characters in char field
Replies
6
Views
356
gdg1976
gdg1976
gdg1976
  • Locked
  • Question
db2 sql extract double rows (different in case sensitive) 1
Replies
6
Views
252
mikrom
mikrom
pratimsingha
  • Locked
  • Question
Alternative of Select count(*) from TABLE
Replies
0
Views
184
pratimsingha
pratimsingha

Part and Inventory Search

Sponsor

Back
Top