Passing integer to a a job activity

[Ragunathan Gunasekaran]

Hi

I am building a sequence that is used for dimensional load.

It contains an Execute command stage with the command Cat and parameters as filepath/file_name.txt. This would give me the maximum surrogate key for a dimension table.

I have to Pass this to another job as in integer, so that the downstream job can pass this parameter to the surrogate key stage.


The problem i am facing is , i am not able to convert the value returned from the Execute command stage to integer ..


Any idea please...

[ray.wurlod]

Search DSXchange for @FM

[vivekgadwal]

Ragunath,

Did you try and pass this return value of the Execute Command activity directly into the downstream job as a parameter (which is declared within the job as an integer)? Is it output-ing an error?

Another way I can think of would be to pass the return value into the downstream job (as a string) and apply a conversion within that job {StringToDecimal}.

You can also keep track of the dimension table surrogate keys by using the state files in surrogate key gen stage.

- Vivek.

[Ragunathan Gunasekaran]

Hi ,

I am not getting any conversion errors . I am stuck very before that as i am not aware how i coud use one such.


I tried to search some routine/transform to convert in the expression editor but I am not getting any integer conversion routines/treansforms in the expression edition .

I am expecting something like StringToDecimal() or an equivalent that would help me.


Any clues please .....

[Ragunathan Gunasekaran]

Hi ,

I am not getting any conversion errors . I am stuck very before that as i am not aware how i coud use one such.


I tried to search some routine/transform to convert in the expression editor but I am not getting any integer conversion routines/treansforms in the expression edition .

I am expecting something like StringToDecimal() or an equivalent that would help me.


Any clues please .....

[Ragunathan Gunasekaran]

Hi

Tried passing the value from the execute command activity stage directly to the job activity which has an integer job parameter.

it throws me below error , which i could infer that job activity is not doing a default conversion ...

Code: Select all

Test_suurogate_seq..JobControl (@Job_Activity_9): Controller problem: Error calling DSSetParam(StartVal), code=-4
[ParamValue/Limitvalue is not appropriate]

[Ragunathan Gunasekaran]

Hi

I have found a function OCONV in the expression editor and used the conversion flag as 'MCN'. This has converted my input string from the Execute command activity stage to Integer in the job activity.

Thanks for the valuable time spent to answer the question

[chulett]

There was no need to convert anything to an integer, as Ray noted in your first reply, your issue was the inclusion of the @FM 'Field Mark' by the Execute Command stage. This needed to be stripped in order to be passed successfully which a search for your 'is not appropriate' error message would have turned up. You've found a way to accomplish that by (essentially) applying the DIGITS transform and removing anything not a numeric digit from the string. The typical advice would be to use Convert or array notation to solve the problem, which would work regardless of parameter type.

[vivekgadwal]

as Ray noted in your first reply, your issue was the inclusion of the @FM 'Field Mark' by the Execute Command stage. This needed to be stripped in order to be passed successfully which a search for your 'is not appropriate' error message would have turned up.

Interesting...I was not aware that DataStage appends @FM when it is returning a value. Does this happens all the time or in certain situations. I am about to build one sequence and I need to test this out...!

[chulett]

It's not that it is appending it, it is important to realize that the stage returns the results in a (possibly multi-entry) dynamic array, so if you literally just use the entire result you'll get at least one field mark delimiter. That's why discussions here mention two methods to deal with that:

TheResult<1>

Convert(@FM,"",TheResult)

The first takes just the first element from the array and could easily take the 2nd or 3rd if need be. The second strips out all delimiters, which works fine when there's only one to work with but may not be the best idea when there's multiple elements.

As an aside, a fairly common 'trick' when dealing with a dynamic array is to Convert() all of the field marks to commas to turn it into a delimited list. Or the opposite can be done to turn a list into an array.

[ray.wurlod]

DataStage converts all line terminators to field marks. In that way it does not matter whether you are running on UNIX or Windows operating system.

[vivekgadwal]

DataStage converts all line terminators to field marks. In that way it does not matter whether you are running on UNIX or Windows operating system.

Thanks Ray and Craig!