Updating records in Oracle on the basis of Max Date.

[sambit]

Hi Guys,
I will appreciate if someone can help me out of this problem. I am trying to update few records ito Oracle thru DS server job as per the requirement mentioned below.

I/P Data :

Cust_Id Ack_Ind Ack_Dt
1 Y 2007-10-15
2 N 2007-10-15

Oracle DB Data :-

Cust_Id Ack_Ind Ack_Dt Load_Dt
1 N 2007-09-15 2007-09-13
1 Y 2007-06-15 2007-05-13
2 Y 2007-10-15 2007-09-13
2 N 2007-06-15 2007-05-13

Requirement :-
I want to update the Ack_Dt and Ack_Ind for the cust_Ids in the table with the value in the I/P file for the max(Load_Dt) as mentioned in BOLD.

Expected O/P in Oracle DB :-

Cust_Id Ack_Ind Ack_Dt Load_Dt
1 Y 2007-10-15 2007-09-13
1 Y 2007-06-15 2007-05-13
2 N 2007-10-15 2007-09-13
2 N 2007-06-15 2007-05-13


It will be great if you can let me know how can I do so thru DS server.

[ray.wurlod]

Aggregator stage?

[sambit]

Thanks Ray for the quick reply. But I am supposed to do that in the Update query where the data in the Oracle DB will be updated for that row only where we have the max(Load-Dt).

I hope I am clear with the requirement.

[sachin1]

hello dear,

job design

database---->T1---> hashfile

lookup from hashfile to transformer T2 (equi join constraint)

inputfile-----> T2-------------------->Oci-stage(User-defined Sql)




you need to first find distinct Cust_Id from database into hash file,
then put a constraint into transformer to get equi-join i mean to say all matching Cust_Id from your input file.

In your Oracle OCI stage use user-defined SQL for updating records,


update table_name set Ack_Dt = (incoming date binding number) where (Cust_Id,Ack_Dt) in (select Cust_Id,max(Ack_Dt) Ack_Dt from table_name where Cust_Id=:1 group by Cust_Id)

it works fine

[sambit]

Thanks Sachin...
all I am facing the problem is in the OCI stage. It is not allowing me to have custom SQL statements. So I am unable to code the update statement with subquery in it.