Is there any way to overwrite the parameter with row values?

[ICE]

Dear All,

Is there any way to overwrite the parameter with row values?
For example: Job parameter vndemail=123@abc.com

row value ->
vndA,vndAemail@123.com
vndB,vndBemail@456.com
vndC,vndCemail@123.com
.
.
.

I would like to overwrite the value of vndemail=123@abc.com with vndAemail@123.com when reading the first row and send email to vndAemail@123.com and when 2nd row and 3rd row reading, I would also like to overwrite the value of vndemail with respective values and send email to respective email which get it from vndemail parameter.

Do you think it can be done in Datastage Server job or Sequencer job???


Thank you for your time,

[ray.wurlod]

Not possible legally. Parameter values are read into memory when the job is started.

If you're not interested in "legally", search DSXchange for references to STAGECOM.ARR.

[Sainath.Srinivasan]

In order to keep it simple without manipulating parameters, you can use following methods.

One method will be to call the sendmail utility in a loop - once for each line.

Did you look into sendmail script ?

You can invoke it from your transformer using OS calls.

[ICE]

Dear Ray,

Ok. I will find it although I do not know what is STAGECOM.ARR.
Is it possible to explain little bit about STAGECOM.ARR???
I am so sorry for my poor knowledge:(


Thank you so much for your time,
ICE

[ArndW]

The STAGECOM.ARR values are not documented and, as Ray has pointed out, there is no official way to do this. Manipulating the STAGECOM.ARR takes some knowledge of the internals of DataStage. I would go the path that Sainath has recommended and use explicit calls from your transform stage to the sendmail script with row data. That is documented, legal and supportable.

[ICE]

Dear ArndW and Saint,

Yeah I really want to use it but my actual rows will be as below.

Order_id, SKU, VndNo,Qty, Price...
001,10001,100,10,10
001,10002,100,10,10
001,10003,100,20,20
002,10001,101,12,12
.
.
.

Then I want to send order no 001 to respective VndNo 100's email. So I want to check the next row's Order_id is same order_id or not. Hence I would like to reset the parameter value with previous row's Order_id to check whether it is same order_id or not.
I am so sorry for my poor example from my first post:(

Is there any idea please???


Thank you so much for your time,
ICE

[Sainath.Srinivasan]

That is different from what you mentioned before.

You can split the data into separate files based on the recipient mail id. After this job, you can browse through the directory and send each file to intended recipient.

PS : Thanks for making me a Saint.

[ICE]

Dear Sainath,

Yes. I am so sorry that I gave poor example and made misunderstanding
Yes. I also want to split the files according to Order_id but I don't know how to split the rows by grouping order_id such as

001,10001,100,10,10
001,10002,100,10,10
001,10003,100,20,20

I want all the above 3 rows to be in order_id=001 file and etc.
Is there any idea how can I split it please???


Thank you,

[Sainath.Srinivasan]

Do you have PX installed ? Reason for this question is that I can give you easy solutions in Unix than in Windows.

In Windows, you can create a unique list of values by passing the data through an aggregator and writing to a separate file. This file can be used in a loop to locate, prepare and email the records from your current output.

[chulett]

Write the files out using a Folder stage, it will create data-driven separate files for you automatically if you set it up properly.

[ICE]

Nope. Mine is 7.5 Window version
May I know how to create unique list of value? Do you think it can be solved as my requirement include duplicate order-id with diff sku?

Thank you so much for your time,

Do you have PX installed ? Reason for this question is that I can give you easy solutions in Unix than in Windows.

In Windows, you can create a unique list of values by passing the data through an ...

[ICE]

I have already tried with folder stage but it could solve only 50% of my requirement. Using Folder stage, files could be created according to Order_id but the problem is the generated file can keep only the last rows of the specific order_id
Actually there should hav more than one rows in the file.

For example:

Order_id,SKU, VendNo,price, qty
001,10001,100,10,10
001,10002,100,10,10
001,10003,100,20,20

File created as 001 for Order_id 001 but it include only the last row which is for sku 10003 as follows.

001,10003,100,20,20

It seems like overwrite.
Is there any way to append the rows in folder stage file creation?

Thank you so much for your time,

Write the files out using a Folder stage, it will create data-driven separate files for you automatically if you set it up properly. ...

[chulett]

I don't recall having any such problem, it should continue to write out all records to your target until the field controlling the name changes. You are sorting the data first by order_id, yes?

[ICE]

Really?Do you mean, the output file should have all the records for that specific order_id?
But my output order files include only one last record for that specific order_id
Yes. I sorted by Order_id.
Is there any idea please???



Thank you so much for your time,

[chulett]

As I said, I don't recall that being a restriction and unfortunately I have no way to run my own tests anymore. And the documentation is a little sparse on the subject, so I'm afraid others will have to actually help you with this. That or ask your official support provider. Sorry.