Page 1 of 1

Maximum of Effective Date

Posted: Tue Nov 30, 2004 7:48 pm
by 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

Posted: Tue Nov 30, 2004 8:49 pm
by 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).

Posted: Tue Nov 30, 2004 8:54 pm
by 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>

Posted: Wed Dec 01, 2004 11:20 am
by 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