Hi,
I have a job design as follows.
Database--------->lookup------->Xfm1---->aggregator-------->TRGDatabse
In the Xfm1 stage I put a dummy row called Key_Col and default the value to 1. In the aggregator stage I group by the Key_col and count the rows and insert it to the TRGDatabse. Every thing works fine except when I have no records from the input. When the lookup results in no result I need to insert count = 0 in the TRGDatabse. I have tried different options in Aggregator stage but I have not been able to handle this scenario. What is the best way to handle this?
Aggregator Output
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 10
- Joined: Sat Dec 05, 2009 2:05 pm
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
There are many ways to handle this - apart from union.
1.) You can create another stream with only one row with a row gen.
That one row can have a dummy column - say value 1 - which you can lookup after your aggregator and then funnel the failure of this link's lookup with your aggregator output.
You will have to include another column
2.) Make the join within the db and send the resulting count to datastage
3.) Use link counts to count the input and output to lookup stage and decide on a dummy job to insert zeros.
1.) You can create another stream with only one row with a row gen.
That one row can have a dummy column - say value 1 - which you can lookup after your aggregator and then funnel the failure of this link's lookup with your aggregator output.
You will have to include another column
2.) Make the join within the db and send the resulting count to datastage
3.) Use link counts to count the input and output to lookup stage and decide on a dummy job to insert zeros.
Or course there are many ways, that was only the opening salvo.
The whole point was to understand that you must process something to have any output. Or as noted, use a job that just handles the 'nothing to do today' part and run one or the other.
![Wink :wink:](./images/smilies/icon_wink.gif)
The whole point was to understand that you must process something to have any output. Or as noted, use a job that just handles the 'nothing to do today' part and run one or the other.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers