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

On Fri, May 17, 2019 at 9:25 AM Timothy P Clark <timclark@xxxxxxxxxx> wrote:

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.

rethink the data model? These are legacy systems, there is no way the
physical file can be changed.

I really appreciate the help. I thought the SQL VIEW was the answer to
these data problems in AS400 databases. Now I do not know how to safely
code an SQL view. The CASE WHEN is not guaranteed to run before the THEN
clause.