DSXchange

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

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 ?

How about something like: The OCI then executes an update statement appropriate to each link.

David

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.

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 guess I wanted to be more royalist than the king ...

I will use the transformer and update both of the columns with the apropriate conditions.

Thank you guys for your help

To be thorough, it could be done with a SQL like:
But the advice above is the right way, so do that.