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

Steve,

I can see where you're coming from. I also get frustrated--usually when
trying to recreate a customer problem for debug--with how the optimizer
seems to make it difficult to get exactly the behavior that I want.
Sometimes I wish I could make it less smart.

I don't know if this will help, but I'll put it out there for general
education...

SQL is essentially a declarative language, not a procedural language.
You're telling the query engine *what* data you want to see, and it is
responsible for *how* it is accessed. From this perspective, the view
definition actually doesn't state to convert the field (a "how") only when
the length is 10. It states that you want returned a converted value (a
"what ") whenever the length is 10 and 0 in all other cases. And since
you've told the optimizer (via the WHERE clause) that the data you want
only includes fields with length 10, you've effectively just declared that
what you want returned is the converted value, nothing more.

For the final/outer select, SQL does have some quasi-procedural rules
about the order in which clauses are evaluated. In those cases, the WHERE
precedes the SELECT. In fact, I'm guessing you'll find that if you run the
SELECT from the view by itself, it will run successfully. That's because
it is now the outer select, and the query engine must eliminate all rows
with length <> 10 before it evaluates the selected fields. But once it's
back in the view--or encapsulated in a CTE--the optimizer is free to move
things around however it wishes in order to obtain the best performance
while still honoring the *declarative* intent of the statement.


Tim Clark
DB2 for IBM i

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 05/17/2019
02:48:13 PM:

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

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/17/2019 02:48 PM
Subject: [EXTERNAL] Re: why data translate error when selecting from

view?

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

On Fri, May 17, 2019 at 3:34 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

I'm curious why you want to stick with the LENGTH(TRIM(A.CPCSID))
instead of TRANSLATE? I would think the TRANSLATE is safer, in that

the

LENGTH statement doesn't guarantee that the date is numeric. I mean,

it

may work with your data, but in the general case it wouldn't.

TRANSLATE seems harder to read and maintain. Also was thinking TRIM
should run quicker than TRANSLATE.

I am still kind of shocked that SQL will not let me safely run a CASE
statement to convert character data to decimal. The code clearly states

to

convert the character field to decimal when the TRIM(LENGTH()) is = 10.