DSXchange

hi,

i have a sequential file stage with input columns :

status varchar
completed_date timestamp

the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.

i used the logic of filtering the columns with status='C' in the 'where' clause and then agreegating. but this did not work since agrregation will not work on time stamp. the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'

need some help on this

Your timestamp string is in a collatable format. Declare it as Char (or VarChar) and get the max of that.

Sgiri1 wrote:status varchar
completed_date timestamp

the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.
the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'

Hi,
You can try this:-
Before passing to aggregator, add one more column for seconds. This will be the difference between your input timestamp and a 'Constant Time stamp'.
* You can use it something like 2009-01-01 00:00:00.000
Then you can use the aggregator to know the min or max timestamp.

the function 'SecondsSinceFromTimestamp'- returns the number of
seconds between two timestamps.

Hi Experts,
If the logic is incorrect, please let me know.

Well, then you'd have to "decode" it back. As noted, declare the timestamp as a string, the format it is coming in is directly collatable so will aggregate just fine.