Re: SQL error handling

Buck,

I have not tried this, but SQL has its peculiarities.

Recently I had a SUBSTR(DIGITS(field), 1, 2) AS week into an outfile,
amongsyt other coulmns/fields. The column/field WEEK in the outfile is
CHAR.
In the next query using this outfile the SQL statement SELECT MIN(Week) + 1
AS Week2, MIN(week) + 2 AS week3, etc., (in a CTE) worked: it calculated a
numeric value for each weeknumber.

So I am thinking of:

CASE WHEN DIGITS(fields) = ' ' THEN 0 ELSE field END AS field
or
CAST (field AS ZONED)

But I am afraid, if SQL touches the field in error it will get into all
states any way.


With regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 3-8-2009 at 15:31 Buck wrote:

I have a complex SQL UNION that is throwing SQLSTATE 22023. This is a

result of blanks in a signed column in one of the 4 files being UNIONed.

There were no instances of this reported until I applied Cume 9104

last week. I agree that SQL should tell me about corrupted data. I

also agree that I need to fix the data; unfortunately it will be weeks

before we find and fix every internally described program that touches

this file and at the moment, the database manager is omitting rows with

the bad columns which isn't particularly useful for my inquiry users.

I have searched the support web site for applicable PTFs. If I could find

the PTF that changed the behaviour, I'd remove it. It'll take me a

long, long time to read every cover letter of every PTF that got applied.

My immediate question: Can I handle SQL column errors more granularly than

having SQL discard the entire row? I expect the answer is 'no' but

I thought I'd ask anyway.