Using WHERE clause on a sub-query populated column

Question

Question

Steve Pisani · Jul 19, 2017

Hi,

Is it possible to use the value of a column that is populated by its own subquery, in the WHERE clause of the outer-query ?

The following fails (it does not parse syntactically):

SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1 WHERE C>10

I guess I could wrap it up as in inner query of it's own - this way (which works) :

Select * from 
(SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1)
where C>10

but I was wondering if there was some syntax in the original snippet that I could do instead.

Thanks - Steve

Discussion (3)2

Log in or sign up to continue

Alexander Koblov · Jul 19, 2017

No. You need to wrap it as you showed in second example.

You can use "C" alias from first sample only in "ORDER BY" clause in the same query, not in WHERE.

0 0

score 0 · Answer 1 · 2017-07-19T09:01:26-04:00

I think that would have to be like

SELECT A, B, C.S1 from Table1, (SELECT S1 FROM Table2) as C WHERE C.S1>10

but it would need something more to define the relationship between table1 and table2.

score 0 · Answer 2 · 2017-07-19T19:26:49-04:00

Steve Pisani · Jul 19, 2017

OK. Thanks for confirming..

S

0 0