Preserving original load date

dataservices job design
1

Hello. I have been trying to work out how to perform an update of some fields, while leaving others untouched, but without success.

I have a target table with date_loaded and date_modified fields, which do not exist in the source table. The target table has a primary key called program_id, which also exists in the source table. For records with new program_ids I would like to insert a record, with the date_loaded and date_modified dates equal to sysdate and this bit works ok, but I cannot see how to not update the date_loaded field for updates. So far, the DF looks like this:

Source Table -> Query Transform (includes additional date_loaded and date_modified fields on RHS of window(Schema Out)) -> Table Comparison (Primary Key = program_id, compare columns all except program_id,date_loaded and date_modified ) -> …

Flow then split into Insert flow and update flow:

A) Insert Flow

…> Mapping Operation - discard all except insert -> Target Table

B) Update Flow

…> Mapping Operation - discard all except update -> Query Transform with date_loaded from Schema Out (RHS) -> Target Table

I step through the workflow in debug mode and all seems ok, with the changed record (change to value in one of the descriptive fields in the source) going down Update flow, but it still inserts a new record, with a NULL in the date_loaded field instead of just changing the descriptive fields, while maintaining the date_loaded value from the existing record.

What I’m essentially trying to do is the equivalent of the following Oracle SQL statement:

UPDATE program set fieldA,fieldB,fieldC = (SELECT fieldA, fieldB,fieldC from source)

rbartley :belgium: (BOB member since 2005-03-31)

2

Okay 99% I think, in your UPDATE Flow, you have given UPDATE–>INSERT in the Map Operation. So it would preserve the existing value and try inserting new value too. So if there is no PK around there, then it would have got inserted.

Just change UPDATE–>UPDATE in your UPDATE Flow and everything should be smooth

ganeshxp :us: (BOB member since 2008-07-17)

3

Hi ganeshxp,

Sorry, perhaps I’m missing something, my update flow already has update only (Update = Update), all others are discarded:

Original post:
…
"B) Update Flow

…> Mapping Operation - discard all except update"

This doesn’t seem to do what I want though. It goes down the UPDATE split but still inserts a new record even though insert is discarded in the MO.

Any ideas?

rbartley :belgium: (BOB member since 2005-03-31)

4

How are you setting the ‘discard all except update’ in the mapping transform? Your update leg is using a query transform and this will only except a ‘normal’ column input. So in your mapping transform set the ‘Output row type’ to normal for an input of ‘update’.

(apologies if you already know this)

Nemesis :australia: (BOB member since 2004-06-09)

5

Hi Nemesis,

The MO options are set as:

For the Update flow:

INPUT OUTPUT
normal normal
update normal
insert discard
delete discard

For the Input flow:

INPUT OUTPUT
normal normal
update discard
insert normal
delete discard

rbartley :belgium: (BOB member since 2005-03-31)

6

Well, that simply explains the problem.

In your UPDATE flow, you are setting UPDATE–>NORMAL so DI will not scan the table for if that PK record is present or not. NORMAL simply means some kind of INSERT. Hope you know that Table Comparison Transform can give only INSERT/UPDATE/DELETE records. So in fact to avoid confusion you could set NORMAL–>DISCARD
In update set UPDATE --> UPDATE &
In insert set INSERT --> INSERT
Then the things should flow through.

ganeshxp :us: (BOB member since 2008-07-17)

7

Ganeshxp,

I have tried both ways. After Nemesis’ post, I changed the Update from Update to Normal for the Update branch and Insert to Normal on the Insert branch. It does not work in either case.

Also, if the TC cannot produce a Normal record, I don’t see how setting Normal to Discard is going to help.

My understanding is that the table is compared solely based on the information provided in the primary key and compare columns sections of the TC transform and then assigns it U, I or D depending on whether there is a new key value or whether any of the values in the compare columns have changed, e.g.

TC Transform

PK Compare Columns
ID ID
Value 1

If source contains

ID Value 1 Value 2
1 2 A
2 3 Z
3 4 Q

and target contains

ID Value 1 Value 2
1 2 A
2 4 Z

I would expect source record 1 to generate no change, 2 to generate a U and 3 to generate an I, irrespective of the value in Value 2.

In my real world case, the TC correctly identifies an update record by flagging with U, but instead of updating all fields except the DATE_MODIFIED, it performs an insert.

Essentially, what I am trying to understand is how to get BODI to do a selective update of the sort that can easily be done in SQL by specifying which columns to update,

e.g. using the example above:

update TARGET SET VALUE1=. I don’t want BODI to force an update of VALUE2 as well.

rbartley :belgium: (BOB member since 2005-03-31)

8

If you have any Query Transforms in between the MO & the Target Table then please remove it. Do all those updates before the TC and not after that. Once you add a Query between your MO and TARGET, then the whole concept of TC/MO is disappearing.

ganeshxp :us: (BOB member since 2008-07-17)

9

Ok. So, I reconfigured the data flow so it is now as follows (also see screenshot attachment:

Source => Split into two flows as below

Flow 1 (Update)

QT with all fields apart from DATE_LOADED
=>
TC
=>
MO (all set to discard apart from UPDATE = UPDATE)
=>
Target

Flow 2 (Insert)

QT with all fields INCLUDING DATE_LOADED
=>
TC
=>
MO (all set to discard apart from INSERT = INSERT)
=>
Target

This had the desired effect, so that updates do not update DATE_LOADED and insert includes all fields (naturally).

Thanks to ganeshxp and Nemesis for responding
Doc1.doc (248.0 KB)

rbartley :belgium: (BOB member since 2005-03-31)

10

Great it worked for you.
Pretty much the similar Design I made to test your issue. I should have posted the screenshot. My bad. :frowning: . Never mind.
Wow I like that snapping tool !!! What is that? Is it SnagIt?

ganeshxp :us: (BOB member since 2008-07-17)

11

Just cropped image in print screen.

Thanks again

rbartley :belgium: (BOB member since 2005-03-31)