By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

Complex expression in select list, where, and order by

P: n/a
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)
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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)

Yes

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
Knut Stolze wrote:
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...

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.
Nov 12 '05 #4

P: n/a
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.

Nov 12 '05 #5

P: n/a
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".

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.