How to fire an update query using datastage job?
Moderators: chulett, rschirm, roy
How to fire an update query using datastage job?
I have few records in a table as shown below ,but due to business rules
I want to update below id 202BILGN16 to 202BILGN12
rest all other rows will be as it is
using a query I can get below recods to update
System Plan Id
P1 102 202BILGN16
P1 102 202BILGN16
P1 102 202BILGN16
P2 103 202BILGN15
P2 103 202BILGN15
P2 103 202BILGN15
but where should I write an update constraint in order to update 202BILGN16 to 202BILGN12
I want to update below id 202BILGN16 to 202BILGN12
rest all other rows will be as it is
using a query I can get below recods to update
System Plan Id
P1 102 202BILGN16
P1 102 202BILGN16
P1 102 202BILGN16
P2 103 202BILGN15
P2 103 202BILGN15
P2 103 202BILGN15
but where should I write an update constraint in order to update 202BILGN16 to 202BILGN12
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
based on your requirement the User Define query should look likequtesanju wrote:I think using below fuction i can achieve this
change
ereplace OR
convert OR
Left OR
Right
Code: Select all
update tablexyz set key3=? where key1=? and key2=? and key3=?
now if you have in input column
key1, key2,key3,newkey3
I think, and if I am not wrong, the key3 in the set statement will always take the third field from input stream, not the 4th field which you want to have. and If I am wrong, You can try this, and let us know if you have succeeded then post here
I achieved it using
Change(columnName, "16", "12")
because I want to change 202BILGN16 to 202BILGN12
here is flow for my job
input query -->transformer-->seq file
after applying Change(columnName, "16", "12")
I checked seq file i found expected changes was there
I'm trying same now on database by changing flow as
input query -->transformer-->database table
Change(columnName, "16", "12")
because I want to change 202BILGN16 to 202BILGN12
here is flow for my job
input query -->transformer-->seq file
after applying Change(columnName, "16", "12")
I checked seq file i found expected changes was there
I'm trying same now on database by changing flow as
input query -->transformer-->database table
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
Your solution has already been posted here - twice now - first by myself and then again by arunpramanik. Read and apply that advice. The only thing we've had to guess on are your actual key fields but I think it's a pretty good guess.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers