aggregator stage

mab_arif16 · Post by **mab_arif16** » Thu Jul 13, 2006 7:56 pm

Hi
Can we impliment this query in data stage

select count(*) ,col1,col2,col3,min(col4) from #table1 (which is output data from transformer) group by col1,col2,col3 where count(*) > 1

Thanks in advance for help

ARif

ray.wurlod · Post by **ray.wurlod** » Thu Jul 13, 2006 9:02 pm

No, but only because it's illegal SQL (not a DataStage issue). You need HAVING instead of WHERE.

That said, your metadata (table definition) requires one Integer column whose derivation is COUNT(*) and four other columns to match the other columns in your query.

Note that, in some databases, Count(ColX) is more efficient than Count(*) depending on the type of index used on ColX.

kumar_s · Post by **kumar_s** » Fri Jul 14, 2006 12:01 am

Yes you can. Sort the data using sort stage. Have KeyValueChange option enabled. Filter out (select only) those whose Key has not changed atleast for once. And do aggregation.
Or simply aggregat and filter based on the additional column.

mab_arif16 · Post by **mab_arif16** » Fri Jul 14, 2006 10:47 am

ray.wurlod wrote:No, but only because it's illegal SQL (not a DataStage issue). You need HAVING instead of WHERE.

That said, your metadata (table definition) requires one Integer column whose derivation is COUNT(*) and four other columns to match the other columns in your query.

.

Yeah you are right ,I am sorry I made mistake while typing.

Thanks