INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Execute Immediate

Execute Immediate

(OP)
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.
 
 
 

RE: Execute Immediate


Try this.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close