Re: SQL error handling

[Buck <kc2hiz@xxxxxxxxx>]

Carel Teijgeler wrote:

> 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.

I tried that but no change; still get the same error.  Reason code 6
says '6 - A data mapping error occurred on the field before the select
or omit operation was attempted' so I'm not surprised by this result.

The really annoying problem is that because the error happens so far
down in the database code, there is no way to diagnose which row is in
error, or for that matter, which table.  The error message always tells
me that the column in the first table in the UNION is at fault, but it
says that even after I have cleaned up that data.

So

Select x from tablea union
select x from tableb union
select x from tablec

will throw this: 'Select or omit error on field (Cast(ReferenceVar(20),
Char Var Len, max = 1 Pad(x40,x4040)) AS Short Zoned(2,0)) member
TABLEA.'  Even if the bad data are in tablec.

Unpleasant.
  --buck