Use subquery from select clause in where clause-CodePudding

I'm writing a query in Oracle SQL and I want to use the result from a subquery in the select clause in the where clause. The custom column sets the value using a case expression. Simple example:

select 
 column_x,
 column_y,
 (case when (select something from table_x) > 1
  then 'Yes' else 'No') "yesno",
 column_z
from
 example_table
where
 yesno = 'Yes'

What could be the approach to achieve this, if it's even possible? Thanks.

CodePudding user response：

Your query is basically fine assuming that the subquery returns no more than 1 row. But you cannot use the column in a subquery. You could, however, move the logic to the from clause:

select t.column_x, t.column_y, x.yesno, t.column_z
from example_table t left join
     (select case when something > 1 then 'Yes' else 'No' end as yesno
      from table_x
     ) x
     on 1=1
where x.yesno = 'Yes';

If the query is really a correlated subquery, then you can use left join lateral.

CodePudding user response：

You could do this:

select 
 column_x,
 column_y,
 yesno,
 column_z
from (
  select 
   column_x,
   column_y,
   (case when (select something from table_x) > 1
    then 'Yes' else 'No') yesno,
   column_z
  from
   example_table
)
where
 yesno = 'Yes'

Note: I removed your double quotes from "yesno" as they force the column name to be case-sensitive, which is undesirable.

Of course, in this example you could also do this:

select 
 column_x,
 column_y,
 'Yes' yesno,
 column_z
from
 example_table
where
 (select something from table_x) > 1