Execute Immediate

[Cenois]

Hi Guys,

The following Code is ran in IBM Data Studio Developer. It gets two dates and sets the firstmonth date to the 16 of the month and the lastmonths date to the 15 of the month. Then it passes these dates into another procedure. That is where i think im getting the error (EXECUTE IMMEDIATE). Thanks for any help!

--THE CODE--
CREATE PROCEDURE PROCEDUREDATE1()
SPECIFIC PROCEDUREDATE1

LANGUAGE SQL

begin

declare SQLSTMT Varchar(1000);
declare BeginDate date;
declare EndDate date;

declare test int;
declare case1 date;
declare case2 date;
declare case3 date;
declare case4 date;


select LastMonth -16 days into case1 from BusinessDate where date_type='CQTR';
select LastMonth -15 days into case2 from BusinessDate where date_type='CQTR';
select LastMonth -14 days into case3 from BusinessDate where date_type='CQTR';
select LastMonth -13 days into case4 from BusinessDate where date_type='CQTR';

select day(LastMonth)into test from BusinessDate where date_type='CQTR';


SELECT CASE
when test=31 THEN case1
when test=30 THEN case2
when test=29 THEN case3
else case4
end case
into EndDate
FROM SYSIBM.SYSDUMMY1;


select FirstMonth +15 days into BeginDate from BusinessDate where date_type ='CQTR';


SET SQLSTMT = 'call ProcedureDate2(?,?,?,BeginDate, EndDate,.97)';
execute immediate SQLSTMT;
end



--THE ERROR--

Run ProcedureDate1 ()

PROCEDUREDATE1- Run started.
Data returned in result sets is limited to the first 50 rows.
Data returned in result set columns is limited to the first 100 bytes or characters.
PROCEDUREDATE1- Calling the stored procedure.
PROCEDUREDATE1- Exception occurred while running:
A database manager error occurred.SQLCODE: -206, SQLSTATE: 42703 - "BeginDate" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.53.71
PROCEDUREDATE1- Roll back completed successfully.
PROCEDUREDATE1- Run failed.

 

[Mercury2]

Try this.

SET EndDate = CASE
   when test=31 THEN case1
   when test=30 THEN case2
   when test=29 THEN case3
   else case4
end CASE;
...

SET SQLSTMT = 'call ProcedureDate2(' CONCAT BeginDate CONCAT ', ' CONCAT EndDate CONCAT ', ' CONCAT .97 CONCAT ')';

execute immediate SQLSTMT;

Not tested but that should work.

Philippe