updating a table outside a mapping

[cognos11]

I have three tables.

1) Accounts - target table in m_accounts
nsf_date

2) Account_transactions - target table in m_account_transactions
transaction_code

3) source_target_xref

Following is Psuedo code which i have written based on functional specs.

IFF
account_transaction.transaction_code in
(select account_id, transaction_code from account_transactions where transaction_code in
(select FLD1_SOURCE_VALUE from source_target_xref where ref_source_system_id = 10 and reference_table_name = 'NSF_TRANSACTIONS'))

Then
Update accounts set nsf_date = max(nsf_date,created_date) where account_id = same account_id's that satisfy above select statement


I want to update( based on the above logic ) the 'nsf_date' field in accounts table while i am running wf_account_transactions which loads account_transactions table.

Do i have to do this through m_account_trasactions mapping or m_accounts mapping.?

I am told not to touch the m_accounts mapping.Is it possible?

My m_account_transactions is a direct 1:1 mapping
Source table : account_transactions_stg(oracle)
target table : account_transactions(oracle)

How do i implement this.

1)do i need to create a stored procedure and call it through stored procedure transformation?
2)If so how do i incorporte that iff condition in that.
3)I am thinking of using a lookup trx for source_target_xref.Do i use a connected or unconnected?

or

You may please suggest a simpler method if there is one


Help appreciated.

 

[bardhaan]

Hi Cognos,

Whatever I've understood from your problem, I'm writing the solution based on that.
If you need to update the field Nsf_Date in Accounts table, you have to have that table as target. Now as you are told not to touch m_Accounts mapping, so I think you have to make a new mapping.
So don't touch any of the mappings m_Accounts or m_Account_Transactions.Make a new mapping with an update strategy transformation.

I'm giving a brief plan about the mapping.
* You didn't mention about the primary key.I consider account_id as the primary key in both the account tables.

a) Have Accounts and Account_Transactions both the tables as source and use a single source_qualifier for those.(I assume that both the tables are in same schema, it seems so from the similarity in their name)
Select the following columns.
Table: Accounts
---------------
Account_Id,
Nsf_Date,
Created_Date

Table: Account_Transactions
------------------------------
Account_Id,
Transaction_Code

Use the join condition:
Account_Transactions.Account_Id=Account.Account_Id

b) Make a lookup transformation lkp_Source_Target_Xerf with the table Source_Target_Xerf. Set FLD1_SOURCE_VALUE, ref_source_system_id, reference_table_name as lookup port.
Connect the Transcation_Code from sq to the lkp and put that in lookup condition i.e. Transaction_Code=FLD1_SOURCE_VALUE.
Specify the conditions in lookup sql override i.e. ref_source_system_id = 10 and reference_table_name = 'NSF_TRANSACTIONS'

c) Use an expression transformation.
Get the following ports:
sq(Account_Id)----------->Account_Id
sq(Nsf_Date)------------->Nsf_Date
sq(Created_date)--------->Created_Date
lkp(FLD1_SOURCE_VALUE)--->FLD1_SOURCE_VALUE

Create another output port with name Final_Date.
Put expression as follows:
IIF(Created_date>=Nsf_Date,Created_Date,Nsf_Date)

d) Create an update strategy transformation.
Get the ports from expression transformation as follows:
Account_Id,
Final_Date,
FLD1_SOURCE_VALUE

Put Update Strategy Expression as follows:

IIF(not isnull(FLD1_SOURCE_VALUE),DD_UPDATE)

e) Import Accounts table as target.
Connect the Account_Id and Final_Date ports from update strategy to Accountd_Id and Nsf_Date in the target table respectively.

I think this will solve your problem.

Please reply in case you have any difficulty.

Regards,
Ani.

N.B. This mapping should run only after the Account_Transaction table is loaded.

 

[cognos11]

Hi Anil

Thanks for such a prompt reply

I didnt make myself very clear.

My objective is While transactions loading is happening, it should update the nsf_date in accounts table simultanseously taking into considerations all the conditions i mentioned above.

I can make modifications on account_transactions mapping not accounts mapping.

Therfore i am assuming that i can just pull accounts table as an additional target into existing account_transactions mapping and also call the source_target_xref as the lookup.

Just to make it more clear i am including the functional spec below.

Specs:
-----

Set nsf_date in accounts from account_transactions load.

When there is a nsf transaction_code in account_transactions,
update accounts set nsf_date = max(accounts.nsf_date,creation_date) of that transaction.

Nsf transaction_codes are defined in source_target_xref.if transaction_code is found in source_target_xref for ref_source_system_id = 10 and reference_table_name = 'NSF_TRNASACTIONS' then that transaction is considered as NSF.

account_id in accounts table is PK
there is no PK in account_transactions table but account_id is NOT NULL.

 

[bardhaan]

Hi Cognos,
You can do one thing.
You can use the mapping I've proposed as a different string in the same mapping designer.

Like
string1: source------------->Account_Transactions(Target1)
string2: source------------->Accounts(Target2)

String1 is the flow currently running.
String2 is what I proposed yesterday.

You can set target load plan according to that i.e. target1 will be loaded first, commit will be executed and then target 2 will be loaded.

I think this will solve your problem.

N.b. If you have the Created date column in Account_Transactions table, you pull out that from the source instead of what I proposed yesterday.

Regards,
Ani