Bob Stearns wrote:

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)

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...

--

Knut Stolze

DB2 Information Integration Development

IBM Germany