I have declared a variable in an initialisation script, and later in the job I have to run a piece of SQL using the variable in the where clause of the statement. How do I do this. I have used all sorts of bracketing. Help.
AndyV 
(BOB member since 2004-01-16)
example please. Where did you use the variable? Where clause? Mapping? SQL Transform? …
And how does the text look like? Probably the datatypes involved.
Werner Daehn 
(BOB member since 2004-12-17)
In the initialisation script I have set the variable as a varchar(8) and the syntax is as follows:
$SNAP_DATE = to_char(to_date(‘31 12 2003’, ‘dd mm yyyy’),‘YYYYMMDD’);
When the job runs, the variable is returning a value of 20031231 as expected.
I have then tried to add the variable to the where clause of a SQL transform, instead of ‘20031231’ so that I can reset the variable and loop the job and run it for various dates, but the SQL Statement errors because the value of the variable is not being passed to the SQL statement.
I have got round the problem by creating a table with one row and one column that contains the value of the variable, and including this table in the where clause instead of the variable, but I would like to know where I am going wrong.
AndyV (BOB member since 2004-01-16)
have you definatly declared the variable as a global variable?
you may have it as a local variable, and this may cause the problem.
bungy 
(BOB member since 2007-10-04)