Does anyone know why I can't use a variable in the stored procedure below. The SP fails at compile time with the message SQL0090 - "Host variable not permitted here". The variable is a date (Str_Dt). If I hard code a date (instead of the variable), the procedure compiles, but not when I use a variable.
Create or Replace Procedure CFO_Daily_sp ( IN Str_Dt Decimal(6,0), IN End_Dt Decimal(6,0) ) Language SQL sp: Begin Declare Str_date Numeric(6,0); Declare End_date Numeric(6,0); Declare global temporary table LastNight as ( Select * from nsbflatsav where substr(flat,1,12) in ( select substr(flat,1,12) from nsbflatsav where substr(flat,16,1) = 'H' and substr(flat,40,2)||substr(flat,32,2)||substr(flat,35,2) = digits(Str_Dt))) with data ; End
The variable in question is "Str_Dt".
Our system is version 7.1 and we're fairly current on the PTFs, except for the lastest technical refresh (TR5) which just came out last week.