Re: why data translate error when selecting from view?

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

Hope that helps,

Tim Clark
DB2 for IBM i


"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 05/14/2019
10:08:55 AM:

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

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/14/2019 10:09 AM
Subject: [EXTERNAL] why data translate error when selecting from view?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

why would selecting from a view result in a data translate error when I

am

able to select all the rows and columns of that view and copy to another
table? ( the view has a WHERE clause which selects rows with valid data

)

table CONTR has a customer number that stored in a character field. And
some of the values in that char field are not all digits. So I create a
view that selects only the rows where that customer number field

actually

contains a customer number. And in the view, I convert the character

cust

number field to a decimal field.

I prove this works by creating a new table by selecting all rows from

the

view:
create table aplusb1fcc/contrpx as (
select a.* from contrv1 a
) with data
Table CONTRPX in APLUSB1FCC created but was not journaled.

but when I select from the view I get an error:
select a.itcd, a.itno, a.startDate, a.expireDate
from contrv1 a
where a.csno = 1346100
and a.expiredate >= current date
Selection error involving field *N.

Select or omit error on field Cast(Translate(CONTR_1.CPCSID, *UNNAMED
Table) AS member CONTRV1.

here is the view:

create OR REPLACE view aplusb1fcc/contrv1 as
select a.cpcono cono,
case when length(trim(a.cpcsid)) = 10
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,
a.cpitcd itcd, a.cpitno itno,
a.cpprcd prcd, a.cpngpr ngpr,

DATE(digits(a.cpstcc) || substr(digits(a.cpstdt),1,2) ||
'-' || substr(digits(a.cpstdt),3,2) ||
'-' || substr(digits(a.cpstdt),5,2)) startDate,

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,

a.cpcnqt cnqt, a.cpprbk prbk

from contr a
where length(trim(a.cpcsid)) = 10 ;
--
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=y3Wi9KB1RnzjMQRq8m_v-

GIT1TYSPNX9Tgetl-uVjQg&s=HpgyDBKzoBphSPbwC3_1yJmSUeGPuwgXLhif5nFXnwM&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=y3Wi9KB1RnzjMQRq8m_v-

GIT1TYSPNX9Tgetl-uVjQg&s=arTJzLJlTTkmGIIBwTBZFqDYH50M3MLijXu8iHPgOSg&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=y3Wi9KB1RnzjMQRq8m_v-

GIT1TYSPNX9Tgetl-uVjQg&s=epPh8RpNNVTRsv6v0bfzwe3v8-kl-o2wrv3AwMuGSm0&e=