Passing data as parameter values

adityavarma · Post by **adityavarma** » Mon Oct 25, 2010 4:23 am

Hi,

I have an requirement here below, but i am unable to get how to implement this.

I have an source table say DW_SRC_TABLE in this i have the below data for the table

Tablebname rule1 value
======== ==== ====
testtable startdt 2010-10-20
tabletest enddt 2010-10-31
and so on...

and this data will differ from day to day..

In my requirement i need to take the values from the above table and pass it to the next job

Job2 will be based on the below queries and this job should be made generic.

select * from testtable where startdt=2010-10-20
select * from tabletest where enddt =2010-10-31
and so on .....

and the testtable and startdt should be parameterized as they will differ from everyday.

The main thing i am not able to proceed is, how to pass the values as a parameters for the next job

Can anyone please suggest me on how to proceed on this?

Thanks
Aditya

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Oct 25, 2010 5:19 am

Search for userstatus.

adityavarma · Post by **adityavarma** » Mon Oct 25, 2010 6:31 am

Step 1: I have written the table output to an sequential file
Step 2: I have done the cat command using the execute command activity
step 3: I have created a uservariable activity and specified the parameters as per the table columns

this is where i got stuck.

my sequential file created is like
SCHEM1,TABLENAME1,STR_DATE,YEAR,1
SCHEM2,TABLENAME2,DATE_TIME,MONTH,2
SCHEM3,TABLENAME3,STR_DATE,DAY,30
SCHEM1,TABLENAME4,STT_DATE,MONTH,30
SCHEM1,TABLENAME5,DT_TIME,DAY,15

Now i want to pass in the above 5 columns as parameters

Field(Trim(Convert(@FM,'', Execute_Command_36.$CommandOutput))," ",1,2)

Can Index function be used to achieve this?

Can you please help me to achieve this?

chulett · Post by **chulett** » Mon Oct 25, 2010 7:05 am

Field function. The last three bits should be ",",1,1 for the first field, ",",2,1 for the second field, etc. In other words: comma separated, field #, # of contiguous fields to extract.

adityavarma · Post by **adityavarma** » Mon Oct 25, 2010 10:02 pm

Thanks Craig,

I have implemented as suggested by you , but i have one issue while i do the cat command the below data is coming and for the last line an extra line is been generated, which is causing issue while peforming the field command

SCHEMA;TABLE1;STRT_DATE;YEAR;1
SCHEMA;TABLE2;DATE_TIME;MONTH;2
SCHEMA;TABLE3;STRT_DATE;DAY;30
SCHEMA;TABLE4;STRT_DATE;MONTH;30
SCHEMA1;TABLE5;DT_STRT;DAY;15
----EXTRA LINE-------

Schema : Field(Trim(Execute_Command_36.$CommandOutput),";",1,1)
TABLE: Field(Trim(Execute_Command_36.$CommandOutput),";",2,1)
DATE: Field(Trim(Execute_Command_36.$CommandOutput),";",3,1)
HISTORY: Field(Trim(Execute_Command_36.$CommandOutput),";",5,1)

The log in the director is
15:48:09: Exception raised: @Job_Activity_47, Error calling DSSetParam(HISTORY), code=-4 [ParamValue/Limitvalue is not appropriate]

Can you please help me on how to remove the last line while performing the cat command.

Regards,
Aditya

[/code]

ray.wurlod · Post by **ray.wurlod** » Tue Oct 26, 2010 12:10 am

The extra line will always be there, because there's a line terminator at the end of the penultimate line. Perhaps you need to detect it being empty with a simple If..Then..Else expression.

adityavarma · Post by **adityavarma** » Tue Oct 26, 2010 12:55 am

I am passing the below cat command output as parameters to the next job in the transformer

SCHEMA;TABLE1;STRT_DATE;YEAR;1
SCHEMA;TABLE2;DATE_TIME;MONTH;2
SCHEMA;TABLE3;STRT_DATE;DAY;30
SCHEMA;TABLE4;STRT_DATE;MONTH;30
SCHEMA1;TABLE5;DT_STRT;DAY;15
----EXTRA LINE-------

when i write the same to the sequential file with the parameters
only the first line is been written to all the lines

"SCHEMA","TABLE1","TRT_DATE","YEAR","1"
"SCHEMA","TABLE1","TRT_DATE","YEAR","1"
"SCHEMA","TABLE1","TRT_DATE","YEAR","1"
"SCHEMA","TABLE1","TRT_DATE","YEAR","1"
"SCHEMA","TABLE1","TRT_DATE","YEAR","1"

Where as i want to pass all the 5 lines as parameters.

The command i am using in the Activity variable is
Field(Trim(Execute_Command_36.$CommandOutput),";",1,1)
Field(Trim(Execute_Command_36.$CommandOutput),";",2,1)
Field(Trim(Execute_Command_36.$CommandOutput),";",3,1)

and so on..

Can anyone please help me on this?

Regards,
Aditya

adityavarma · Post by **adityavarma** » Tue Oct 26, 2010 4:05 am

Ray,
Thanks for the reply

What if then else statement can be written to find the line terminator at the end

Can i write something like this

If Field(Trim(Execute_Command_36.$CommandOutput),";",5,1)=@FM Then Field(Trim(Execute_Command_36.$CommandOutput),";",5,1) Else Field(Trim(convert(@FM,',', Execute_Command_36.$CommandOutput)),';',5)

Regards,
Aditya

chulett · Post by **chulett** » Tue Oct 26, 2010 6:44 am

If there are always 5, youshould just need the @FM convert on the last one:

Code: Select all

Field(Convert(@FM,'', Execute_Command_36.$CommandOutput),';',5,1)

Trim optional. And convert the Field Mark to an empty string rather than a comma.