Hello -
I am trying to built an SP to dynamically create an MQT with parms.
this following code is failing...
Please help!
thanks,
cristi
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