Hi guys,
My source file format is : Key;Data;Type
My target table format is : Key;NAT or Key;REG (NAT and REG having the same format)
Is it possible depending on the Data Type to update either of the columns or the other :
I am using OCI stage, I tried smthg like that but it didn't work :
UPDATE TAB
SET CASE
Type = 'NAT' then NAT=:2
Type = 'REG' then REG=:2
end
WHERE Key=:1
Thx for your help
Conditionnal update
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As far as I am aware, CASE is only possible in the SELECT clause of a SELECT statement. I am unaware of it's being legal in an UPDATE statement.
Can you execute such a statement legally in TOAD or sqlplus ?
Can you execute such a statement legally in TOAD or sqlplus ?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
How about something like:
The OCI then executes an update statement appropriate to each link.
David
Code: Select all
Transform ---- (Constraint: Type = 'NAT') ----> OCI
| ^
--------------(Constraint: Type = 'REG') ------|
David
Re: Conditionnal update
akhelif wrote: UPDATE TAB
SET CASE
Type = 'NAT' then NAT=:2
Type = 'REG' then REG=:2
end
WHERE Key=:1
You have to update all columns, they need to be bound because of the laws of SQL. You can't be switch columns in your set, you have to have each column defined. You can always update a column with its existing value. Using a stored procedure you can also do this one row at a time using execute immediate with a dynamic SQL statement.
However, you can't do this using DataStage. I suggest you prepare this using the transformer stage and a reference lookup.
Putting transformation logic in the SQL defeats the point of using an ETL tool. You should really do this using the graphical design metaphor.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: Conditionnal update
I guess I wanted to be more royalist than the king ...kcbland wrote:akhelif wrote: UPDATE TAB
SET CASE
Type = 'NAT' then NAT=:2
Type = 'REG' then REG=:2
end
WHERE Key=:1
You have to update all columns, they need to be bound because of the laws of SQL. You can't be switch columns in your set, you have to have each column defined. You can always update a column with its existing value. Using a stored procedure you can also do this one row at a time using execute immediate with a dynamic SQL statement.
However, you can't do this using DataStage. I suggest you prepare this using the transformer stage and a reference lookup.
Putting transformation logic in the SQL defeats the point of using an ETL tool. You should really do this using the graphical design metaphor.
I will use the transformer and update both of the columns with the apropriate conditions.
Thank you guys for your help
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
To be thorough, it could be done with a SQL like:
But the advice above is the right way, so do that.
Code: Select all
UPDATE TAB
SET NAT = CASE Type WHEN 'NAT' then :2 ELSE NAT END
, REG = CASE Type WHEN 'REG' then :2 ELSE REG END
WHERE Key=:1
Ross Leishman