Complex expression in select list, where, and order by

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 26 Oct 2005 21:35 GMT

Is there an easy way (without duplication of the complex expression) to
use the same complex expression in all three places?

Will something like this work?

WITH (SELECT t.*, <complex expr> AS x FROM <base_table> AS t) AS wt
SELECT * FROM wt WHERE ABS(x)<10 ORDER BY ABS(x)

Reply to this Message

Serge Rielau - 26 Oct 2005 23:35 GMT

> Is there an easy way (without duplication of the complex expression) to
> use the same complex expression in all three places?
[quoted text clipped - 3 lines]
> WITH (SELECT t.*, <complex expr> AS x FROM <base_table> AS t) AS wt
> SELECT * FROM wt WHERE ABS(x)<10 ORDER BY ABS(x)

Yes

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Knut Stolze - 27 Oct 2005 08:48 GMT

As Serge said, this will work, but you have to get your syntax straight.
Either you stick with sub-selects:

SELECT *
FROM ( SELECT t.*, <complex expr> AS x
FROM <base table> AS t ) AS wt
WHERE ABS(x) < 10
ORDER BY ABS(x)

or use common table expressions:

WITH wt(..., x) AS
( SELECT t.*, <complex expr> AS x
FROM <base table> AS t )
SELECT *
FROM wt
WHERE ABS(x) < 10
ORDER BY ABS(x)

or you use a function or whatever...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Bob Stearns - 27 Oct 2005 16:06 GMT

>>Is there an easy way (without duplication of the complex expression) to
>>use the same complex expression in all three places?
[quoted text clipped - 24 lines]
>
> or you use a function or whatever...

Thank you. I haven't started using WITH yet, this is my first need, so I
appreciate the syntax tip! As far as keying goes, the subselect seems
easier (I only have the use of 1 hand) so a question that naturally
occurs is whether WITH is always transformable into a subselect and what
is the relative efficiency of the two constructs.

Reply to this Message

juliane26 - 28 Oct 2005 11:23 GMT

it is the same efficiency, just the look and feel is different.

With 'WITH' it is easier to read, e.g. when having three subselects
nested or so.

It will be explained like a subselect anyway.

Reply to this Message

Tonkuma - 29 Oct 2005 03:02 GMT

In Bob's example, both way get same result.
"WITH common-table-expression" is more capable. Same result of
"common-table-expression" can be used more than once in a query. For
example, you can do self-join common-table-expression. And recursive
query can be done by "common-table-expression".

Reply to this Message