time variant tables

[sumeet]

How Do i update dates in time variant tables. i know that normal update cant be done.
and delete recs whose key is date feild.

Thanks
Sumeet

[rleishman]

I don't really understand what you are asking.

How do I "update" a (Type 2) Slowly Changing Dimension?
If so, are you asking how to set up the DS job, how to configure the Relational stage (OCI / ODBC / DB2, etc), or how SCD2 is supposed to work theoretically?

Or are you just asking how to perform an UPDATE in a relational stage when a date is part of the primary key?
If so, then I can help:
- make sure the datatype of the Date column is Timestamp or Date
- set the "key" attribute of the PK columns to "Yes"
- ensure the "key" attribute of nonkey cols is "No"
- set the Update Action on your Relational Stage to "Update Only"
- Look at the SQL it generates for the UPDATE statement. Since DS passes dates from stage to stage as strings, it will include a string=>date conversion something like

Code: Select all

AND datecol = TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')

- Ensure that you are passing the date from the preceding stage in this format.

[sumeet]

i am asking about updating a type 2 SCD .

Actually the problem is the current date in the table is wrong and i have to update the correct date.

But the table is time variant table. i.e. type 2 SCD if I am not wrong.

Thanx
Sumeet

[rleishman]

Since you are double-posting in the OraFaq forum as well, I'll assume you're using OCI.

You will have to use a user-defined query:

Code: Select all

update tabname
set eff_date = :3
where key = :1
and eff_date = :2

And pass 3 variables into the stage: key, old_eff_date, new_eff_date.

But if this is a one-off to fix incorrect data, why don't you just do it in SQL*Plus.

[sumeet]

but this is general update statement . will this work for time variant table

thanks

[rleishman]

Have you tried?

[ray.wurlod]

If it's really a Type 2 SCD you insert a new row. The only column you update in the old row is expiry date, or whatever other method you might use to indicate that it's an expired row. Indeed, a Type 2 SCD doesn't need any such column; the row with the maximum surrogate key value for a particular business key value is always the current row for that business key.