438,609 Members | 2,246 Online 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.*, AS x FROM AS t) AS wt SELECT * FROM wt WHERE ABS(x)<10 ORDER BY ABS(x) Nov 12 '05 #1
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.*, AS x FROM 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.*, AS x FROM 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.*, AS x FROM AS t ) AS wt WHERE ABS(x) < 10 ORDER BY ABS(x) or use common table expressions: WITH wt(..., x) AS ( SELECT t.*, AS x FROM 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) touse the same complex expression in all three places?Will something like this work?WITH (SELECT t.*, AS x FROM 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.*, AS x FROM AS t ) AS wt WHERE ABS(x) < 10 ORDER BY ABS(x) or use common table expressions: WITH wt(..., x) AS ( SELECT t.*, AS x FROM 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. 