DSXchange

Is your incoming date format mm-dd-yyyy without any leading or trailing spaces?

Is that the complete data you are working with? Three columns? If yes then group by on eid and name and take the max date.

If TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '0000-00-00' Or TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '' Then StringToDate('0001-01-01',"%yyyy-%mm-%dd") Else StringToDate(lnk_user_dataset.ENROLL_DT,"%yyyy-%mm-%dd") I dont see you substring'ing the first 10 chara...

chulett wrote:I thought (from other posts here) that DB2 wanted dates in internal format.

Thats true for server. For px it has to be a date.

Take the first 10 characters from you input data and grind it through the StringToDate() function.
For input anomalies like '0000-00-00', get the business rule and apply accordingly.

Do you want it converted into Date or timestamp?

Dump the hashed file into a text file and pivot that. See if the error persists.

You are looking for a full outer join between the two. Look at this topic
viewtopic.php?t=124978&highlight=full+o ... join+stage

Instead of continuing to accept requests via email, create a shared folder, ask folks to drop off the files there, create an ftp script to get the data onto your server and then run your process.

I dont think its the machine. It may be the data variance between the two environments.

Either order by in your source stage or, explicitly using the sort stage. Then feed that into the aggregator. The input tab of the aggregator lets you state what fields are sorted and in what order. Sort on the fields that are used in the aggregator as group by.

Sort the data on the grouping keys, inform the aggregator of the the sorted data.

Do not substring to get the second column. use a field function. or perhaps a shell script.

A lot of factors can play into database deletes. The tables primary key may be foreign keys in several different tables. It has to go check for the existence of child before actually deleting the record. Then depending upon the constraint definition, it may be delete cascade or do nothing. If this i...

My rule of thumb is, if I am working with an external FLAT file and I have to do null checks, I perform isnull() and len(trim()) < 1.