I have a source and a target. I want to make a post session failure command based on the record count of my source and target tables.
I have 3 sources and a target with 3 instances of it. I want a post session failure, i.e. after the session succeds, if we find there is a mismatch in record count of source and target, then the workflow should fail.
I think it can be done through a bteq script. If we give a bteq script in 'Post Session Failure Command' in Session properties, it can be done.
I have prepared a sql query for it
sel( ( sel count (*) from src_tab_1) + sel count (*) from src_tab_2) + sel count (*) from src_tab_3) - sel count (*) from target_tab))
if result of the query is zero its fine, else there must be a post session failure.
Can you please provide me with the bteq script for the above sql query.
You just have to add a HAVING:
sel( ( sel count (*) from src_tab_1) + sel count (*) from src_tab_2) + sel count (*) as x
from src_tab_3) - sel count (*) from target_tab))
having x <> 0;
.if activitycount = 1 then quit 33;