Re: why data translate error when selecting from view?

was able to isolate the problem down to the WHERE clause. But it still
makes no sense to me.

here is the view.
when I create the view with this WHERE clause I can select from it without
an error:
WHERE TRANSLATE(RTRIM(A.CPCSID),' ',' 0123456789') = ' '

I change that WHERE clause to:
where length(trim(a.cpcsid)) = 10
I cannot select from the view. Get a translate error when translating
CPCSID from char to decimal.

create OR REPLACE view aplusb1fcc/contrv2 as
select A.CPCSID,
case when A.CPCSID <> ' '
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,
case when a.cpexdt = 0 then date('2050-12-31')
else
DATE(digits(a.cpexcc) || substr(digits(a.cpexdt),1,2) ||
'-' || substr(digits(a.cpexdt),3,2) ||
'-' || substr(digits(a.cpexdt),5,2)) end expireDate
from CONTR a
-- WHERE TRANSLATE(RTRIM(A.CPCSID),' ',' 0123456789') = ' ' ;
where length(trim(a.cpcsid)) = 10 ;

here is the SQL select statement that gets the translate error:

select a.csno, a.expireDate
from contrv2 a
where a.csno = 1346100
and a.expiredate >= current date
Selection error involving field *N.

Message . . . . : Select or omit error on field
Cast(Translate(CONTR_1.CPCSID, *UNNAMED Table) AS member CONTRV2.
Cause . . . . . : A select or omit error occurred in record 3217,

here is an SQL that shows the value of CPCSID in record 3217 in the table.

select a.cpcsid, length(trim(a.cpcsid)) lgth
from contr a
where rrn(a) = 3217

Customer LGTH
ID
110A 4

notice the value is 110A. and the length is 4. But the view is selecting
only rows where the value in CPCSID has a length of 10.

the point is, there is no difference in the WHERE clause when I use
TRANSLATE or LENGTH to select rows where CPCSID contains decimal data. Why
does the LENGTH(TRIM(a.CPCSID)) method fail?



On Tue, May 14, 2019 at 3:11 PM Timothy P Clark <timclark@xxxxxxxxxx> wrote:

Hi Steve,

The data translate error is showing up in the view because of the
selection criteria. The query engine has to evaluate the "bad" data in
order to process the selection criteria. But when you're selecting
everything directly from the table (without a WHERE clause) the query
engine doesn't need to do anything with the bad data except pass it
through to the output buffer. For performance, the query engine doesn't do
any more processing of the data than is absolutely necessary