Conditionnal update

[akhelif]

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

[ray.wurlod]

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 ?

[djm]

How about something like:

Code: Select all

Transform ---- (Constraint: Type = 'NAT') ----> OCI
  |                                              ^
  --------------(Constraint: Type = 'REG') ------|

The OCI then executes an update statement appropriate to each link.

David

[kcbland]

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.

[akhelif]

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

[rleishman]

To be thorough, it could be done with a SQL like:

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

But the advice above is the right way, so do that.