HOW TO: Assign a column value to a global variable

[Halaku]

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.

I'll appreciate suggestions and solutions.

Thanks,

MI

[TimStearn_SASProductManagement_]

MI,

I don't know all the details of what you're trying to do, but if this value needs to be passed between jobs, your best bet is to write it to a table or file.

Thanks,

Tim Stearn

[Halaku]

Thanks Tim,
But I am wondering if I can assign to global variable whose value I am passing in the inner jobs. And what makes you suggest temp table option, is global variable use is not stable in SAS?

Thanks for taking time to reply.

Misbah.

[LinusH]

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?

/Linus

[Halaku]

This is the workflow:

Job1: Retrieve the SEQUENCE NEXTVAL from Oracle
Job2:Process data using the NEXTVAL value
Job3: Process data using the NEXTVAL value
Job4: call Job1 then LOOP Job2 & Job3 END LOOP

What I intend to do is to retrieve the NEXTVAL in Job1 and pass to global variable g_logid
and then use the &g_logid in the expression section of TABLELOADER throughout.

Thnaks,

Misbah.

[LinusH]

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.

/Linus

[Patrick]

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

[Michael_SAS]

Have you tried CURRVAL? it returns the current value of a sequence. here is an example.

create sequence seq_customers;

select seq_customers.nextval from dual;
NEXTVAL
----------------------
2
select seq_customers.currval from dual;
CURRVAL
----------------------
2