Is it possible to pass a job parameter inside another?

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 3:54 am

Hi,

Here's what im looking for

let say i have i have a parameter by the name
parcode = 'abc'
and i have another parameter
partranslation = 'alphabets #parcode#'

when i use the parameter partranslation in my transformer it is displaying "alphabets #parcode#" instead of "alphabets abc".
Is there a way in which i can get to DS to parse a job parameter which is within another parameter??

Thanks,
Abhinav

BugFree · Post by **BugFree** » Tue Aug 04, 2009 4:03 am

try in transformer --> 'alphabets' :#parcode#

BugFree · Post by **BugFree** » Tue Aug 04, 2009 4:06 am

One Parameter value directly into another parameter is not possible. If you have to achieve this, use a sequence activity variable stage to conctinate as mentioned in the earlier post and pass that as to the job.

Is that answers your question?

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 4:16 am

wish it was that simple.

The example i gave was just to for ur understanding.

Lets say i have something like

partranslation ='select abc from #parcode# table where table.col1 = #par1#
and table.col2 = #par2#'

In this case what am i supposed to do??

Thanks,
Abhinav

miwinter · Post by **miwinter** » Tue Aug 04, 2009 4:24 am

If you can't achieve this "parameters within parameters" functionality, to produce full SQL on the fly, I think you will have to look instead at doing this upfront and passing the finalised SQL query as one final parameter to the job, with a call to this parameter as your user-defined SQL statement.

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 4:30 am

Well.. tht can be done but im not looking for a work around for this issue..We have a new project coming up which will require us to build a huge number of jobs.. if i can get this to work it greatly reduce our coding effort.

Thanks,
Abhinav

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 04, 2009 5:25 am

Rather than using one parameter within another, you can concatenate both in the query as

Select ...
From ....
Where fieldname = '#parameter1# #parmaeter2#'

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 5:35 am

Thanks for the suggestion sainath... but the entire query keeps changing depending on the user requirment. So i will not be selecting the same columns everytime.

case when for a specific output i ,might use the query

case 1

select name,deptid from #parcode# table where table.col1 = #par1#
and table.col2 = #par2#'

case 2

select deptname,branch from #parcode# table where table.col1 = #par2#
and table.col2 = #par1#'

So here my entire query is dynamic.

Would appreciate it if u could help me out in such a case!

Thanks,
Abhinav

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 04, 2009 5:50 am

You have options

Use 2 new parameters - say par3 and par4 and pass the corresponding values upfront from the calling sequencer depending on the case.

Use par3 and par4 in place of par1 and par2. So the new parameters will vary dynamically depending on your case.

miwinter · Post by **miwinter** » Tue Aug 04, 2009 5:50 am

So it's an issue of the columns you are selecting back changing each time then? Parameterise them.

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 6:08 am

@Sainath
i was not able to understand ur solution.. how will the use of two extra parameters solve my problem..

The queries that i posted are a mere sample of the original ones..

The original queries are more than 500 lines each and there are multiple places where i need to replace the parameters..

@miwinter
I dont have fixed number for each query, one query might have 100 columns the other may have 200 so i cant as such parametrize them..

miwinter · Post by **miwinter** » Tue Aug 04, 2009 6:50 am

I don't see why you can't. Either the parameter passes in a string of 100 column names or a string of 200 column names.

yabhinav · Post by **yabhinav** » Tue Aug 04, 2009 7:03 am

ya.. how stupid of me..!!

will try doing tht

chulett · Post by **chulett** » Tue Aug 04, 2009 7:56 am

So, your plan is to have a single job that can process any number of columns from any number of tables? And then what do you need to do with these columns? Hopefully nothing in the way of transformations if you think RCP is going to be your big saviour here.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 04, 2009 5:32 pm

yabhinav wrote:The example i gave was just to for ur understanding.

Ur was a city in ancient Babylon; it has not existed for thousands of years and is therefore probably beyond understanding anything - even assuming that a city can understand at all.

The second person personal possessive pronoun in English is spelled "your".

Please strive for a professional standard of written English on DSXchange. It helps particularly those whose first language is not English. English is hard enough to understand without idiosyncratic abbreviations.