Maximum of Effective Date

[Dee]

Hi,
I am new to Datastage. I need to retreive Maximum(EFFDT) value based on a condition for my update statement. I have read the earlier messages about using an aggregator stage or input MAX(EFFDT) in the selection tab of input stage. Is there any other way to do it without using either the aggregator stage or the user defined sql. Please let me know.
Thanks in advance,
Deepa

[ray.wurlod]

Use generated SQL. Edit the derivation column in the grid of column definitions so that it contains the requisite MAX function. View the SQL to ensure that the result is what you desire (you may also need to add a GROUP BY clause on the Selection tab's Other Clauses field).

[davidnemirovsky]

Hi Deepa,

Welcome!

There is way to achieve what you are trying to do without using either either the aggregator stage or the user defined sql.

In your database stage, go to the 'Output' tab. Choose 'Generated SQL Query' for the Query Type drop down.

Then in the 'Columns' tab place the MAX dervation into the 'Derivation' column for the field 'EFFDT'.

Ie.
<table border=1>
<tr><td>Column Name</td><td>Derivation</td></tr>
<tr><td>EFFDT </td><td> MAX(EFFDT)</td></tr>
</table>

[Dee]

Hi,
Thanks a lot for the quick response. I did change the derivation as mentioned and used the GROUP BY clause in the Selection tab's Other Clauses field to make it work. The map is working fine.
Have a good one,
Deepa