Aggregator Function Removing Dates

JezT · Post by **JezT** » Mon Oct 04, 2004 6:52 am

I am attempting to pass some data through an aggregator for grouping and summing purposes but appear to have found a problem in that the aggreagtor seems to remove the value stored in one of my fields which happens to be a date field.

The value in the sequential file is held as '2004-07-29' and it is a DATE type field. However, when it passed through the aggregator, the following error appears:

Code: Select all

Value treated as NULL
Attempt to convert String value "2004-07-29" to Date type unsuccessful

Not sure why it states that it is attempting to convert "String Value" as the field is held as a Date.

kcbland · Post by **kcbland** » Mon Oct 04, 2004 7:30 am

Because the aggregator is stupid. Set your metadata to string or "internalize" your date using the ICONV function, then externalize the date after the aggregator using OCONV. The easiest is to just set the metadata to string if your date is guaranteed in ISO format.

chulett · Post by **chulett** » Mon Oct 04, 2004 7:35 am

Technically, everything under the covers is a string in DataStage and also a Sequential file has nothing but Char data in it. Can't say why exactly it doesn't like your date format off the top of my head, but if you define it as a Char(10) you won't have problems.

How much confidence do you have in the format you are receiving it in? There are things you could do to validate or rearrange incoming "date" fields from a flat file, but your incoming format (as is) is good for ordering or maxing or whatever you may be doing in your Aggregator besides grouping.

Suggestion would be to only declare it as a Date just before it goes into the target - and then ensure it is properly formatted for the particular target. Not everything out there likes YYYY-MM-DD for dates.

EDIT - Ok, now I see why it is happening.

Didn't realize the aggregator is "stupid" as I almost always handle dates as strings through them. Either that or as Timestamps... don't recall having issues with those.

kcbland · Post by **kcbland** » Mon Oct 04, 2004 8:03 am

It's stupid because it's trying to be smart and fails miserably. The toolmakers insist on foisting internal date requirements eradically/sporadically throughout the tool. Some stages need it, others don't.

Imagine you have an Oracle OCI stage writing to an aggregator then to a sequential file. In order to pass a date value thru, you have to do an "ICONV" on it going into the aggregator and an OCONV coming out just to see what looks like a date in the output file. Now, if you were using the DB2 plugin, it doesn't return a date that looks like a date, but the internal value and you don't have to touch it going into the aggregator. But coming out to the sequential file you will have to do an OCONV. Now, instead of an aggregator you use a transformer, then Oracle OCI returns what looks like a date, DB2 doesn't, and you don't have to play with anything to write out what looks like a date. There's lots of examples of these "inconsistencies".