Re: Re: why data translate error when selecting from view? -- MIDRANGE-L

Hi Steve,

What release are you running? Does the error occur if you do a
select * from contrv2
or is it only when additional predicates are added?

I'm trying to reproduce this on 7.3 and am not having any luck. There
certainly appears to be a problem, but I can't put my finger on it. You
might have to open a PMR and let support grab traces.

Tim Clark
DB2 for IBM i

From: Steve Richter <stephenrichter@xxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/14/2019 04:00 PM
Subject: [EXTERNAL] Re: why data translate error when selecting from

view?

Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?

u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=xRpZzBA2xpxvwLoNMHyeVEvUHbrft17dnjoIuf5Bma0&e=

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?
u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=jmG3VZgcVMbKXoJNY_h-

Dxrj18wu1uWikmF0wCuSDos&e=.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

https://urldefense.proofpoint.com/v2/url?
u=https-3A__amazon.midrange.com&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=7QcYtj6SSrw4GrN_yNzxJtOqVczTuOhFuXQKEwKBcW4&e=