Need to find sum(sal) for all the active employees

praveenk · Post by **praveenk** » Fri Mar 11, 2011 7:05 am

Hi,

i have a requirement of finding sum of salaries for all the active employees,
i'm finding it difficult with aggregator stage as it needs group by column where i do not need to group by, is there any other stage which supports me to get the required output.

chulett · Post by **chulett** » Fri Mar 11, 2011 7:22 am

Find or create a static value to group on.

abhilashnair · Post by **abhilashnair** » Fri Mar 11, 2011 7:31 am

Aggregator will need a group by column. So you have to have one.
even for doing this outside DataStage by using a sql, uwill need a group by column

Vidyut · Post by **Vidyut** » Fri Mar 11, 2011 7:34 am

You must be having some column to filter out "active employees"
You can use the same column as your key in aggregator.
For Example: Status_Flag = 'Y'

praveenk · Post by **praveenk** » Fri Mar 11, 2011 7:36 am

chulett wrote:Find or create a static value to group on.

please if you can explain me more clearly.

Vidyut · Post by **Vidyut** » Fri Mar 11, 2011 7:43 am

Or If you don't have any such column and consider all rows in the table as active employees then you can just create one more temp column with any default value and the you can use it as your grouping column.

chulett · Post by **chulett** » Fri Mar 11, 2011 7:49 am

That would be the 'create' part I mentioned.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 11, 2011 2:53 pm

Use a Column Generator stage to generate a constant (cycle with single value) and group by that.

praveenk · Post by **praveenk** » Sat Mar 12, 2011 2:02 am

I have created a static variable with a constant value in transformer and in aggregator ive grouped by that variable, now im getting the desired output but my question in performance perspective is this the best way?

praveenk · Post by **praveenk** » Sat Mar 12, 2011 2:12 am

How can i generate a constant value in column generator-- im setting properties as
-column method----> explicit
-column to generate --->col1

which is not giving the constant output, any help greatly appreciated

chulett · Post by **chulett** » Sat Mar 12, 2011 8:31 am

I don't have any way to check directly but it should be a 'Generate' option and the need to set it to something other than 'Cycle'... I think. Poke around and see if you can set it to generate a constant value... or perhaps cycle a number by 0?

And there's no "performance" problem with solving this using a transformer.

jwiles · Post by **jwiles** » Sat Mar 12, 2011 9:46 am

Click on the Output->Columns, then right-click on the generated column select Edit Row...

Generator->Algorithm

Choose your type. The algorithm types available vary depending on data type. For char/varchar and this job, I would choose Cycle and give it only one value to cycle through. The other type is Alphabet and you provide a string of characters for it to cycle through (again, for this job just enter one character). Other data types should be self-explanatory.

Regards,

chulett · Post by **chulett** » Sat Mar 12, 2011 10:04 am

Ah... there you go - cycle through one value. Thanks James.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 12, 2011 2:13 pm

I'm sure I specified "cycle with one value" earlier.

chulett · Post by **chulett** » Sat Mar 12, 2011 6:29 pm

Why, look at that... yes you did. Guess we should read your pithy replies once in a while.