Re: can this reject bad data?

On 5/6/2014 11:26 AM, Hoteltravelfundotcom wrote:

There is some bad data in our system. there is a lack of
validate checking. in this case, can this sql reject for processing
any such bad dates? bad dates are defined as month > 12 and days accordinlgy


SELECT
ALL T01.UAUSRN,
SUBSTR(DIGITS(UAFLWD),5,2) CONCAT '/' CONCAT
SUBSTR(DIGITS(UAFLWD)
,7,2) CONCAT '/' CONCAT SUBSTR(DIGITS(UAFLWD),3,2) AS
FOLLOWUP,
(T01.UAENT#), T01.UASFX#, T02.ADENTN, T01.UANOTT,
T01.UANOTL, T02.ADLNM, T02.ADFNM,
T01.UAFLWD
FROM ASTDTA.NOTEHDUA T01 INNER JOIN
ASTDTA.ADRESsad T02
ON UAENT# = ADENT#
AND UASFX# = ADSFX#
WHERE UAFLWD BETWEEN 20000101 AND 20991231
AND UAPRGD < 1
AND UANOTT = 'E'
ORDER BY 001 ASC, 008 ASC

So the first place to start is to note that your system is not storing
dates, it is storing numbers that humans interpret as dates. The
computer does not realise these numbers are a date, so the answer to
your immediate question is no. No, this SQL will not omit 'bad dates'
for you. You specified a range of numbers to select and a number like
20141306 falls between 20,000,101 and 20,991,231.

If you need to omit 'bad dates' you are going to have to do it yourself.
There are several ways to go about it. I'd write a UDF that tests
UAFLWD; if not a legal date, return a flag noting that. Then your SQL
would look something like

WHERE isDate(UAFLWD) = '1'
and UAFLWD BETWEEN 20000101 AND 20991231
AND UAPRGD < 1
AND UANOTT = 'E'

--buck