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
Maximum of Effective Date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
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>
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>
Cheers,
Dave Nemirovsky
Dave Nemirovsky