Updating Records in Table

[pradkumar]

Hi

I am having my job as follows:

I/P -->TX(lookup + constraint in TX for INSERT AND UPDATE)-->(NEWKEY GENEARTION(for insert new rows) --> Target table

I am adding a flag in TX SAYING THAT IT IS NEW OR NOT (using "Y" for NEW and "Y" for OLD). Initially I gave "Y" for flag and inserted all records in target table.

My question is if Flag = "N", then it is an old record and needs to be updated. How to accomplish this?

[ray.wurlod]

Another link into the database stage, with the rule "update existing rows only".

[pradkumar]

Right now I am having only two links and
I am giving the rule
for Insert as : Insert new rows only
for UPDATE AS: Update existing rows only

Is this correct

[ShaneMuir]

I am having my job as follows:

I/P -->TX(lookup + constraint in TX for INSERT AND UPDATE)-->(NEWKEY GENEARTION(for insert new rows) --> Target table

My question is if Flag = "N", then it is an old record and needs to be updated. How to accomplish this?

Right now I am having only two links and
I am giving the rule
for Insert as : Insert new rows only
for UPDATE AS: Update existing rows only

Is this correct

Looks/sounds good - but I do have one question - how are you determining your flag?

[pradkumar]

For First Insertion I am assigining the Flag to "Y"..(NEW)
If user changes the current_ind in target table to "N"
my job should be update only that record with flag set to "N"

[pradkumar]

For First Insertion I am assigining the Flag to "Y"..(NEW)
If user changes the current_ind in target table to "N"
my job should be update only that record with flag set to "N"

[ShaneMuir]

For First Insertion I am assigining the Flag to "Y"..(NEW)
If user changes the current_ind in target table to "N"
my job should be update only that record with flag set to "N"

I only ask as the above assumes that you have the key to update an existing record.
I just thought i would check that this is the case, as you mentioned that the key is generated on insert. So long as you have the key then you should have no problems.

[sb_akarmarkar]

Right now I am having only two links and
I am giving the rule
for Insert as : Insert new rows only
for UPDATE AS: Update existing rows only

You have done correctly by taking 2 links.

make flag column also as key column in datastage.

keep in insert link Flag = 'Y' and Update link Flag = 'N'


Thanks,
Anupam

[pradkumar]

For First Insertion I am assigining the Flag to "Y"..(NEW)
If user changes the current_ind in target table to "N"
my job should be update only that record with flag set to "N"

[ShaneMuir]

For First Insertion I am assigining the Flag to "Y"..(NEW)
If user changes the current_ind in target table to "N"
my job should be update only that record with flag set to "N"

Yes I got this bit the first time.

To update your target table though you will need the key for the row to update. If you are looking for a flag of 'N' in the target table how are you firstly identifying the row to look for? Is the key of the target table available in your input?

[ray.wurlod]

What are the constraint expressions for the two links in your Transformer stage? What is the derivation expression for your flag (the actual expression, not what it does)? What, precisely, is the problem that you are trying to solve?

[pradkumar]

At present these are the expressions which I am using for Insert and Update


Insert : IsNull(Customer_lkp.Customer_KEY)
Update: Not(IsNull(Customer_lkp.Customer_KEY))

[ray.wurlod]

In that case you don't need the "Yes"/"No" flag.

[shilpa79]

while updating why donot you compare the lookup and I/p columns
and the current Indicator=' Y' and If ur getting the same columns it will not update anything.If any columns changes then it will get updated
with the end_date and Ind="N"

I donot know whether I am on the same page.