0

Let's say I have this following query:

select
*,
case when x in ('x','y','z',...) then 'X' end as value_x
from(
  select *,
  case when condition1 = true
       case
         when field2 == 'xxxx' then substr(field2, 0, 2)
         when field2 == 'yyyy' then substr(field2, 0, 2)
         when field2 == 'yxyx' then substr(field2, 0, 2)
         ...
       end
  end as value_x
 from table) as subquery;

When I do this, I end up getting 2 columns named value_x. Is there any way that I can filter value_x with the IN function without having to use it in every single case? There are a lot of cases, and it would really bloat up the query.

The query-subquery structure can't be changed, as there are dozens of those cases that use values from the subquery to create new columns, this is the first time that I have to update one of the columns coming from the subquery, and I can only use a SELECT statement.

I'm using PostgreSQL.

Eklavya
  • 17,618
  • 4
  • 28
  • 57
Bernardo Lima
  • 1
  • 4
    Don't use `select *` in the outer query, select only the columns you want –  Apr 01 '20 at 18:12
  • [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Parfait Apr 01 '20 at 18:18
  • Thing is, I want every column. – Bernardo Lima Apr 01 '20 at 18:21
  • 1
    ...or give it a different name... so you'll have `value_x` and `value_y`. – The Impaler Apr 01 '20 at 18:31
  • The SQL Standard only offers you the `*` wildcard to select all columns. If you want anything different, you'll need to write the full comma-separated subset of columns. – The Impaler Apr 01 '20 at 18:33
  • I want every column, and I can't have value_x and value_y. There are hundreds of columns in the subquery, so if I had to write select (every single column except value_x) in the outer query, it'd get uglier than writing the IN statement for every value_x in the subquery. – Bernardo Lima Apr 01 '20 at 18:44

0 Answers0