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

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,