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!

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

MQT IN STORED PROCEDURE

Status
Not open for further replies.

cristi22

MIS
Aug 17, 2005
155
US
Hello -

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

Please help!

Code:
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
 

And what is(are) the ERROR(s)? [ponder]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
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
 
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
 

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

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Hello all -
thanks for your time.

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

Yes, look at 'EXECUTE IMMEDIATE' in your manual. [thumbsup2]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Any one please?!

how do i change 2005 to input parameter???

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top