Re: why data translate error when selecting from view?

I'll try to fill in some of the details that were a little vague in my
earlier reply.

You can think of the query engine "inlining" the view into the final
select statement.

Something like this (I'm eliminating the date stuff since it's not
relevant here):

create OR REPLACE view qtemp.contrv2 as
select A.CPCSID,
case when A.CPCSID <> ' '
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno
from qtemp.CONTR a
where length(rtrim(a.cpcsid)) = 10 ;

select csno
from qtemp.contrv2 a
where a.csno = 1346100;

becomes to the optimizer:

select
case when A.CPCSID <> ' '
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end
from qtemp.CONTR
where
length(rtrim(a.cpcsid)) = 10
AND
case when A.CPCSID <> ' '
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end = 1346100;

Keeping in mind that SQL has no rules about predicate evaluation order, it
should be clear that if the optimizer chooses to evaluate the 2nd
predicate before the first predicate, we can run into the error you're
seeing.

As I noted earlier, this is happening on my system because the 2nd
predicate is pushed down to the index scan while the 1st predicate is
evaluated "later", on the table probe.

But when the first predicate is the translate(...) predicate from the
view, the optimizer can push both predicates down to the index scan node.
When this happens, a FALSE answer from the translate(...) predicate
prevents/hides the evaluation of the 2nd predicate. Which I think is what
you want.

Is that more clear?

Tim Clark
DB2 for IBM i


"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 05/16/2019
02:14:11 PM:

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

<midrange-l@xxxxxxxxxxxxxxxxxx>,

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

On Thu, May 16, 2019 at 1:10 PM Timothy P Clark <timclark@xxxxxxxxxx>

wrote:

With the index, the optimizer chooses to evaluate the problematic
[CASE...END] = 1346100 predicate before it evaluates the
LENGTH(TRIM(CPCSID))=10 predicate. It does this because it splits the

data

access into an index scan and a table probe. For performance, it

pushes as

much selection into the index scan as it can. But because char fields

are

stored in the index without trailing blanks, the optimizer has to wait

to

evaluate the LENGTH of a char field until has the "real" data from the
table.

On the other hand, the TRANSLATE predicate can be evaluated in the

index

scan node. There's no need to defer its evaluation until later. This
allows it to be evaluated (to FALSE) before the problematic

[CASE...END] =

1346100 predicate. This gives you the behavior that you are seeing

(and

expecting.)

but the CSNO column from the view is run thru the CASE statement. Which
only translates to decimal if the length is 10:

select a.cpcono cono,
case when length(trim(a.cpcsid)) = 10
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,

I can post the VIEW code again on Monday. How to code the VIEW

correctly

using LENGTH and TRIM?

thanks,
--
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=0Rqj0wZiIVMHvwpNuJVttG3Q2pdzzOwJOW_wvKXpsYc&s=HjrLZovL9AoI_1nFi_zKIvs5ci7_phqmKXcVTDw1a5I&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=0Rqj0wZiIVMHvwpNuJVttG3Q2pdzzOwJOW_wvKXpsYc&s=91EZpjTs3LMW7_DmHz8uOJwGBY62Wqy0ycKkFpxEo0c&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=0Rqj0wZiIVMHvwpNuJVttG3Q2pdzzOwJOW_wvKXpsYc&s=YXXZOlo1eq77ZSytvFdhajK1_i_oqVm-

Olkv7uDhiQQ&e=