DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 20
Location: Chennai
Points: 190

Post Posted: Thu Jan 11, 2018 12:42 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi all,
I got requirement to change the below existing query in to a DS Job

PFB the sample query

INSERT INTO Table_Name (A, B, C, D, E, F, G, H)
VALUES ((SELECT MAX(id) + 1 FROM id_table), '', 5, 'ABBEY', '', 1, 1, 'EFG');


How can I achieve inserting the first value(select query from a table) using the DS job.
Kindly help

_________________
Thanks
Nibu Mathew Babu
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42470
Location: Denver, CO
Points: 218443

Post Posted: Thu Jan 11, 2018 12:45 pm Reply with quote    Back to top    

Your source would be the "SELECT MAX(id) + 1" from the table noted and then you would use another stage (say, a transformer) to add the other hard-coded values to the row before sending it to the target.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 20
Location: Chennai
Points: 190

Post Posted: Thu Jan 11, 2018 3:01 pm Reply with quote    Back to top    

Thanks Craig for the swift response but there is a small change in requirement

if you see the above query, the value for field A should come from select query, B,C,D,E,F Values will come from a source file
columns G,H will be again from transformer(Hard coded)

The whole idea is there are some fields from an input file they need to add some more fields which static value(hard coded from transformer) and a key value for the row,(which is our select maxid query)

Kindly suggest,

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 300

Points: 2966

Post Posted: Fri Jan 12, 2018 10:00 am Reply with quote    Back to top    

you will need to take care here due to parallel processing. if you query the table and get max is 100, then try to insert 10 records on 4 nodes, you will then insert 101 4 times, 102 4 times, and 103 2 times if you don't handle the parallel logic properly.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42470
Location: Denver, CO
Points: 218443

Post Posted: Fri Jan 12, 2018 3:04 pm Reply with quote    Back to top    

For multiple records that need to start with MAX+1 and (I assume) keep incrementing with each row, I'd suggest a different approach. I'd look into sending that current MAX value into the job a ...

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours