× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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=




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.