Passing Parameter from table

dsuser_cai · Post by **dsuser_cai** » Mon Jun 29, 2009 1:51 pm

Hi I would like to know, if there is a way to pass a value from a table as parameter. For example we use sequence as the primary key so for the first time if the sequence is 1, 2, 3....100. Then the next time it should start from 101, 102, 103, ... etc. We have a surrogate_key table where we store the max(key/Seq). Can any body suggest how to retrieve this value (key/seq) from the surrogate_key table and use it in the job? Can we use a routine?

I don't want to use any database stage to retrieve this. Please advise me.

chulett · Post by **chulett** » Mon Jun 29, 2009 2:52 pm

Database table means "database stage" to retrieve. Why not use one? Read the value, stick it in USERSTATUS and then anything downstream can use it as a parameter fairly easily via a Sequence job.

springwtr · Post by **springwtr** » Mon Jun 29, 2009 3:20 pm

Craig,

My issue posted earlier today was similar.
I read about USERSTATUS in documentation, but wasn't clear.
Could you please elaborate on it's usage.

Let us say, I am checking for a flag value ('Y' or 'N') from a DBstage, would I check, jobname.USERSTATUS='Y' on the stage's outer link? I mean USERSTATUS can be used to check column value from previous stage or stage status itself?

Thanks,
Spring

chulett · Post by **chulett** » Mon Jun 29, 2009 4:37 pm

USERSTATUS is a 'single value' job level area that anyone can query after-job and see what is in there from the last run. Sequence jobs are the easiest way to leverage that, use the 'Insert Parameter Value' helper in any stage downstream of a Job Activity stage to see that <StageName>.$UserStatus is one of the available options.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 29, 2009 5:18 pm

IN the server job you will need an interlude function to the DSSetUserStatus() routine.

Code: Select all

FUNCTION UpdateUserStatus(Arg1)
Call DSSetUserStatus(Arg1)
Ans = Arg1
RETURN(Ans)

springwtr · Post by **springwtr** » Tue Jun 30, 2009 11:47 am

I can read table value in the first job. But cannot assign the value to userstatus in the same job. I can only use or assign userstatus in the next job activity.

So then how can I pass the first job's column value to next job/sequence?

Thx,
Spring

chulett · Post by **chulett** » Tue Jun 30, 2009 1:18 pm

You assign it in the current/same job using the posted routine and then you reference it in any downstream job or activity.

springwtr · Post by **springwtr** » Tue Jun 30, 2009 2:09 pm

Craig,

What is Posted Routine? I have pre and post subroutines in the transformer that i cannot use for this purpose.
Thanks so much for replying , I am so curious to get this working....

Thanks,
Spring

chulett · Post by **chulett** » Tue Jun 30, 2009 2:33 pm

By "posted routine" I meant the UpdateUserStatus routine code that Ray posted in this thread. If you do an exact search for "Call DSSetUserStatus(Arg1)" you'll find that various incarnations of it have been posted close to a dozen times in these forums, most include explanations on how to leverage it.

springwtr · Post by **springwtr** » Tue Jun 30, 2009 2:51 pm

Oh, I see..

That's a problem for me.
I couldn't see UpdateUserStatus routine posted by Ray as it was hidden under premium content.

That's a bummer. Is there any other way I can access that code.
I would appreciate it if you or someone could send it to me in an email?

Thx,
Spring

chulett · Post by **chulett** » Tue Jun 30, 2009 3:06 pm

As Ray would say, that's a problem that is easily solved. A Premium Membership would quickly pay for itself with the time it saves, you could have had this 'problem' solved within moments of coming online and searching. Heck, that's why I paid for one for the last three years.

Did you try the search I mentioned? Not everything here is "hidden" which is why I suggested it to you.

springwtr · Post by **springwtr** » Tue Jun 30, 2009 4:11 pm

FUNCTION UpdateUserStatus(Arg1) Call DSSetUserStatus(Arg1) Ans = Arg1 RETURN(Ans& ...

The above is the code I got from Ray.

I got similar code : $INCLUDE DSINCLUDE JOBCONTROL.H
Call DSSetUserStatus(Arg1)
Ans = Arg1

Looks like this one is using header files and stuff.

Both of them would function same way, right? Am I missing a piece of code from Ray?

Also, I use this function and assign the derivation to a column in the job.
Regardless of what column I assign this function value to, Userstatus is set in this job and is available to next job activity in the sequence. Am I correct?

I am going to buy Premium membership soon

Thanks,
Spring

ray.wurlod · Post by **ray.wurlod** » Tue Jun 30, 2009 4:52 pm

There's no need to include a header file if you don't use any of the content of the header file.

Since DSSetUserStatus is a subroutine and is cataloged as DSSetUserStatus then there's no actual need for JOBCONTROL.H.

chulett · Post by **chulett** » Tue Jun 30, 2009 8:25 pm

springwtr wrote:Also, I use this function and assign the derivation to a column in the job. Regardless of what column I assign this function value to, Userstatus is set in this job and is available to next job activity in the sequence. Am I correct?

Yup.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 30, 2009 10:50 pm

chulett wrote:Yup.

I understand that to be an affirmative declaration in the United States of America.