DSXchange

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.

Find or create a static value to group on.

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

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'

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

please if you can explain me more clearly.

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.

That would be the 'create' part I mentioned.

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

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?

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

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.

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,

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

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

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