CODEWITHSUNDEEP
postgresqlcolumn-alias
Indrajeet Gour
Indrajeet Gour

Reputation: 4500

PostgreSQL: ERROR: column "bb" does not exist

I am not good at SQL, I wanted to take MAX using partition by in the following query, but when I use the same query without where clause of that max drive column it says that column does not exist but if I remove the column from where I can see in select the same column is present.

select 
MAX(case when total_split_count = 0 or total_split_count is null then total_split_count  else 1 end) OVER (PARTITION BY ia.col1,ia.col2,ia.col3,ia.col4,ia.col5,ia.col6) as bb
from audits.tbl_name ia
where bb = 1

ERROR: column "bb" does not exist Position: 304

where bb = 1 ^ 1 statement failed.

but the query runs with where clause:

select 
MAX(case when total_split_count = 0 or total_split_count is null then total_split_count  else 1 end) OVER (PARTITION BY ia.col1,ia.col2,ia.col3,ia.col4,ia.col5,ia.col6) as bb
from audits.tbl_name ia

Note: I created that column at run time through "as".

Upvotes: 1

Views: 304

Answers (1)

Tomáš Záluský
Tomáš Záluský

Reputation: 12040

The alias defined in select clause in not visible in where clause. Use

select * from (select ... as bb from audits.tbl_name ia) x where bb = 1

or CTE:

with x as (select ... as bb from audits.tbl_name ia) select * from x where bb = 1

Upvotes: 5

Related Questions