Selecting Max(date) column with multiple rows

[adityavarma]

Hi,

I have a requirement to select only records with latest date for a code.
Below is the example:

code seq_no ref_date
------ -------- ----------
101 100 20100803
101 100 20100804
100 101 20100803
200 100 20100805
201 100 20100804

The ouput should be like below:

100 101 20100803
101 100 20100804
200 100 20100805
201 100 20100804

Can you please help with this?

[satyanarayana]

SELECT code, seq_no, ref_date from <TABLE NAME> OUTT
WHERE ref_date =(SELECT MAX( ref_date ) from <TABLE NAME> INN
WHERE INN.code=OUTT.code )

[mouthou]

If you meant in Datastage and your source is a file, it can be done using Aggregator stage by taking of the max(ref_date) and grouping by code & seq_no

[adityavarma]

If you meant in Datastage and your source is a file, it can be done using Aggregator stage by taking of the max(ref_date) and grouping by code & seq_no

Thanks for your reply.

Yes, I meant in Datastage and source is a file. I have tried with Aggregator Stage, but got errors.

Aggregator_22: Error when checking operator: It is not legal to set a reducer (ref_date) to be the same as a key field,
when the type of that field (string) is not a double.

[mouthou]

What are the key columns you defined in the column definition. I guess you kept ref_date also a key. You cant keep ref_date since it is part of your aggregation function (max())

[adityavarma]

No, I have not kept ref_date as Key column. Only code is the key column.

[chulett]

An exact search for "It is not legal to set a reducer" should answer your question.

[mouthou]

hello chulett,

The search didnt yield much of a resolution to this issue. One post talked about using the key which has been clarified by adityavarma as the column 'ref_date' is not defined as the key.

The other post talked about a workaround of using Sort and De-dup etc. Do you think it is the only solution.

thanks,

[priyadarshikunal]

It would be nice if you give some details on what you have done to achive this.

[chulett]

A "reducer" is a function which causes fewer rows to exit than entered, sum() for example. The error should mean you are trying to sum on a grouping (key) field.

That's my understanding anyway.

[allavivek]

Hi ,

As per my knowledge the output of Aggregate stage will be double ...

As the PO is using max for date field , should we enable preseve type to true in aggr stage, so that the out put will be date data type...

.....

[PhilHibbs]

As the PO is using max for date field , should we enable preseve type to true in aggr stage, so that the out put will be date data type...

What's this about a "preserve type" option? I'm trying to do exactly this - get the max value of a date over a set of data - and I'm getting a Double field as the result. Any tips on how I get this back into a date? Should I do it the longhand way, by converting the date to a number (days since somewhen) and then back into a date afterwards?

[priyadarshikunal]

Output of min() and max() functions are by default dfloat (double) and hence if it needs to be done of columns of different datatype, preserve datatype flag is used. The flag is just to make datastage preserve the datatype instead of defaulting it to dfloat.

It can be found in sub property of 'Column for calculation'.

[chulett]

You should be able to find that option buried in the stage somewhere, Phil, I wish I could be more specific and tell you exactly where. Another option for your max date would be to convert it to external ISO format, a string in YYYY-MM-DD format for example and then max() that in the stage. You could always convert it back afterwards.

[PhilHibbs]

It can be found in sub property of 'Column for calculation'.

Nope, no "Preserve type" attribute...