I have a work flow composed of 4 nested jobs.
In one of the jobs I am retrieving Oracle SEQUENCE NEXTVAL and insert into a table. However I need to carry the same value through out the entire work flow and carry it over to other jobs for processing. I know how to assign a global variable to a column value using EXPRESSION in the mapping but I need to do the reverse here in this case. I tried a code in post code but that advances the SEQUENCE value to its next value and that is not acceptable.
Please try more exactly describe your job.
You have an outer job, with four inner jobs, right?
Are they run in sequence or in parallel (using the loop transform)?
Is it in the first job that you retrieve this value?
Does the rest of the jobs use tha same value, or does all jobs need to regenerate it (for next job)?
You said about writing to a table "and that is not acceptable". Why is that?
Are you using Loop for actual looping, or just a way of nesting jobs?
If the later, you could just nest job without the Loop transform (if you are in DIS 4.X). In this scenario, there are no SAS/CONNECT remote sessions launched, just expanded code in a single SAS session, which means that global macro variables are available in each job.
I agree with Linus: If you're calling job2 & job3 only once then just add them to the flow of job1 without a loop transformation.
This way you basically create one big job which executes in a single workspace. So whatever you define in job1 will be available for the included jobs (job2 & job3).
You might want to set "source2" option as well.
If you have to loop - meaning running job2 & job3 several times with different parameters - then you would need a loop transformation. You possibly would create a control table (one row per parameter value) as input for the loop transformation, under parameter mapping you would assign this control table field to a macro variable, and you would have to parameterize job2 & job3 (done via a hidden prompt which uses the macro variable defined in the loop transformation).
Message was edited by: Patrick