RE: can this reject bad data?

Last word...

Here are things that I learned doing the "less than elegant" SQL I wrote.

I had never needed to find the remainder of division in SQL...now I know that the MOD scalar will tell me this value.

TIMESTAMP_FORMAT will convert "bad" date values stored as numbers in such a way that one cannot test the failures using CASE, COALESCE or IFNULL tests. BTW - I view this as a bug at this point.

The OP provided an SQL example where the number-as-date needed validation. I incorrectly thought that the TIMESTAMP_FORMAT scalar might get the OP closer to the answer. Rob was correct in pointing out my error. The failure cannot be monitored for within the scope of the select, unless you incorporate a UDF to perform the task. So I simply wanted to find a way to validate the number-as-date was a date.

This was never a debate on RPG vs. SQL. The restriction to use only SQL was my own. Meant to challenge me.

After coding with RPG for > 30 years, doing it with RPG would have been too easy. Even so, a production version of this tool would likely be a hybrid of RPG and SQL. Now that I learned a bunch of new techniques (you only see where I finally stopped, not the path of dead ends that got me there)...I'll likely just put the experience on the shelf...waiting for an appropriate requirement for one of the many things that I learned doing this task this way.

I’d do it again in exactly this way...for the challenge! The only difference will be that I will not share it here...it isn't worth it.

Steve Needles


________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.

TRVDiscDefault::1201