MQT IN STORED PROCEDURE

[cristi22]

Hello -

I am trying to built an SP to dynamically create an MQT with parms.
this following code is failing...

Please help!

CREATE PROCEDURE BASYS.TAX_AMT_TBL_test (  IN CHK_YY VARCHAR(4) )
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- CHK_YY
------------------------------------------------------------------------
P1: BEGIN

    DECLARE cursor1 CURSOR FOR
create table test_tax_sum_member_05 as(
select
 r.membno
, max(ISSUED_NAME)  as ISSUED_NAME
, max(ISSUED_ADDR1) as ISSUED_ADDR1
, max(ISSUED_ADDR2) as ISSUED_ADDR2
, max(ISSUED_ADDR3) as ISSUED_ADDR3
, max(ISSUED_ZIP)   as ISSUED_zip
, sum( r.amount )   as net_amount
, sum(r.monthly_amount) as monthly_amount
, sum(r.lump_amount)    as lump_amount
, max(r.fund) 		as latest_fund
, sum(w.wh_amount) 	as wh_amount
--, case r.status when 'V' THEN max(action_date) else max(issue_date) end as latest_date
from pnr_reg r left outer join pnr_reg_wh_type w on r.pnr_reg_key = w.pnr_reg_key
where
r.status =   'V'  and year(ACTION_DATE ) = 2005
or
r.status in ('A','DD','P','PD','PR')and year(ISSUE_DATE ) = CHK_YY
and r.membno not in (SELECT federal_id_number FROM FUNDS
where federal_id_number is not null
--and  r.membno=
)
group by
 r.membno
)DATA INITIALLY DEFERRED REFRESH DEFERRED;
--refresh table test_tax_sum_member_05;
END P1

thanks,
cristi

 

[LKBrwnDBA]

And what is(are) the ERROR(s)?

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cristi22]

BASYS.TAX_AMT_TBL_test - Build started.
DROP SPECIFIC PROCEDURE BASYS.SQL061012135047200
BASYS.TAX_AMT_TBL_test - Drop stored procedure completed.
Create stored procedure returns -104.
BASYS.TAX_AMT_TBL_test: 9: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "test_tax_sum_member_05" was found following "SOR FOR create table". Expected tokens may include: "JOIN". LINE NUMBER=9. SQLSTATE=42601

BASYS.TAX_AMT_TBL_test - Build failed.
BASYS.TAX_AMT_TBL_test - Roll back completed successfully.

i think i'd need to creat udf ...
thanks,
cristi

 

[MarcLodge]

Cristi,

Did you really mean to have coded the DECLARE CURSOR statement? Seems a bit odd to have a CREATE TABLE embedded in a DECLARE CURSOR, plus I can see no cursor processing (open etc.)

Marc

 

[LKBrwnDBA]

I doubt you can embedd a DDL statement in a cursor, try either executing alone (outside cursor) or use an 'EXECUTE IMMEDIATE' statement.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cristi22]

Hello all -
thanks for your time.

I need the dynamic mqt created based on the parameters. Is it possible?

 

[LKBrwnDBA]

Yes, look at 'EXECUTE IMMEDIATE' in your manual.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cristi22]

Any one please?!

how do i change 2005 to input parameter???

CREATE PROCEDURE BASYS.testme ( )

P1: BEGIN

create table tax_sum_member_05_test as
(
    select t1.* from
	(
	    select
     r.membno
 	, max(ISSUED_NAME)  as ISSUED_NAME
	, max(ISSUED_ADDR1) as ISSUED_ADDR1
	, max(ISSUED_ADDR2) as ISSUED_ADDR2
	, max(ISSUED_ADDR3) as ISSUED_ADDR3
	, max(ISSUED_ZIP)   as ISSUED_zip
	, sum( r.amount )   as net_amount
	, sum(r.monthly_amount) as monthly_amount
	, sum(r.lump_amount)    as lump_amount
	, max(r.fund) 		as latest_fund
	, sum(w.wh_amount) 	as wh_amount

	from
		pnr_reg r left outer join pnr_reg_wh_type w on r.pnr_reg_key = w.pnr_reg_key
		inner join tax_1099_FUNDS FD on (R.FUND=FD.FUND)
where
 LEFT(r.status,1) =   'V' 
[red] and year(ACTION_DATE ) =  2005 [/red]
	group by
		 r.membno
  )
	T1 left outer join
	(
		SELECT WTH_MEMBER ,NAME FROM WH_TYPE_CODE  ,MEMBER
		WHERE MEMBER_KEY=WTH_MEMBER
	)
	 t2
	on t1.MEMBNO=t2.WTH_MEMBER
	where t2.WTH_MEMBER    is null
)DATA INITIALLY DEFERRED REFRESH DEFERRED;
--REFRESH table tax_sum_member_05;
--refresh table tax_sum_member_05_test;
END P1

thanks,
cristi