Aggregation

raju_chvr · Post by **raju_chvr** » Sun May 23, 2004 4:51 pm

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 ?

chulett · Post by **chulett** » Sun May 23, 2004 5:13 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sun May 23, 2004 7:08 pm

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.

GIDs · Post by **GIDs** » Sun May 23, 2004 9:14 pm

You can give a count(*) in the original statement itself, instead of in a sub-query

ray.wurlod · Post by **ray.wurlod** » Sun May 23, 2004 9:16 pm

Not so easy where the sub-query is a UNION or INTERSECTION etc.

chulett · Post by **chulett** » Sun May 23, 2004 10:04 pm

At worst case, just wrap a 'select count(*) from' around the whole darn thing.

GIDs · Post by **GIDs** » Mon May 24, 2004 1:37 am

ray.wurlod wrote:Not so easy where the sub-query is a UNION or INTERSECTION etc.

I think Raju was specifically asking for count(*) "coming from specific join condition" and it DOES work for any kind of join even if one of the tables within the main select is a subquery itself!!

roy · Post by **roy** » Mon May 24, 2004 8:01 am

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,