Re: Re: why data translate error when selecting from view?

Well, you can do things like

...
case when length(trim(a.cpcsid)||trim(a.cpcsid)) = 20
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,
...
where length(trim(a.cpcsid)) = 10

The optimizer isn't (yet) smart enough to recognize that
length(trim(a.cpcsid)||trim(a.cpcsid)) = 20 is equivalent to
length(trim(a.cpcsid)) = 10, so it won't consider the when clause to be
covered by the selection predicate.

But at the point that you start having to try to trick the optimizer into
not optimizing the query (and the purpose of all of the transformations
that I've described is to optimize performance), it's maybe time to think
about improving the data model you're working with. Whenever I see casts
and case statements that try to work around an inconsistent data model, I
know the customer is almost always paying a hidden cost--whether in query
performance or in technical debt. I realize that the data model may be
outside of your control, but that really is the best long-term answer
here.

Hope that helps,

Tim Clark
DB2 for IBM i


"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 05/17/2019
07:28:40 AM:

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

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/17/2019 07:29 AM
Subject: [EXTERNAL] Re: why data translate error when selecting from

view?

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

Tim,

could you show me in this view what changes have to be made to make it
work? I understand if I replace LENGTH(TRIM(A.CPCSID)) with TRANSLATE,

it

will work. But how to structure the view while retaining LENGTH and

TRIM?

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,
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 length(trim(a.cpcsid)) = 10 ;

thanks,

-Steve

--
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=ivzQ9v0WL94EsR9tb4TAaYlUlk0Mps4w4LA1M4UxLhc&s=9-

qQSWG2Fv5KkQPb1-jfqY1d3itgK9JyDFG_RWGgiWc&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=ivzQ9v0WL94EsR9tb4TAaYlUlk0Mps4w4LA1M4UxLhc&s=BZKLR12tIEcFfSQGxN0hBP1Wzl7Lx31i2R80cZaxBqI&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=ivzQ9v0WL94EsR9tb4TAaYlUlk0Mps4w4LA1M4UxLhc&s=sZUavrlBL1e8-

RacswwwPaRip1CYVkWpxgNX7LmH0ng&e=