How can I use DB stage to calculate number of records coming from specific join condition. This stage has some metadata, but is it possible for me to just write as Select Count..fromTable A..with some where condition.
Any suggestions ?
Aggregation
Moderators: chulett, rschirm, roy
Well, seeing as how you can 'select' pretty much anything using a DB stage you can select a simple count. Depends on exactly what you are trying to accomplish.
If you are joining tables and want to process the detailed output plus know how many records were involved, you've got a few options:
* Make use of the @INROWNUM or @OUTROWNUM variables, if possible.
* Split off another link with a single field and Aggregate it using COUNT.
* Have a second input link that does the same join with a 'count(*)' in the derivation field and then merge it into your job later.
If all you want is the count, then simply put that in the originating stage. As I mentioned, using a single column with Column Generated SQL and 'count(*)' in the derivation field will get you what (I think) you are looking for.
If you are joining tables and want to process the detailed output plus know how many records were involved, you've got a few options:
* Make use of the @INROWNUM or @OUTROWNUM variables, if possible.
* Split off another link with a single field and Aggregate it using COUNT.
* Have a second input link that does the same join with a 'count(*)' in the derivation field and then merge it into your job later.
If all you want is the count, then simply put that in the originating stage. As I mentioned, using a single column with Column Generated SQL and 'count(*)' in the derivation field will get you what (I think) you are looking for.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you're using user-defined SQL you can do whatever you wish. If all you want is a count, the Columns grid should have only one column, of type Integer.
SELECT COUNT(*) FROM ( horribly complex select statement ) is one way, depending on the database you may need to declare that the inner query writes its results into a temporary table with an AS clause or similar.
SELECT COUNT(*) FROM ( horribly complex select statement ) is one way, depending on the database you may need to declare that the inner query writes its results into a temporary table with an AS clause or similar.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi,
to add on the above,if you just want to know how many rows went thru the output link of a DB stage at run time post execution you can check the link row count with the DSGetLinkInfo Routine.
if you want it pre execution simply copy the sql from the stage, make changes as needed (parameters if any and such) and instead of columns use the count(*).
the thing is that you better off using your DB specific tools and possibly want to get you DBAs to optimize this sql for you if possible.
IHTH,
to add on the above,if you just want to know how many rows went thru the output link of a DB stage at run time post execution you can check the link row count with the DSGetLinkInfo Routine.
if you want it pre execution simply copy the sql from the stage, make changes as needed (parameters if any and such) and instead of columns use the count(*).
the thing is that you better off using your DB specific tools and possibly want to get you DBAs to optimize this sql for you if possible.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org