aliased column is ambiguous in where clause

Database
Fan

aliased column is ambiguous in where clause

Is there a way to reference an aliased column so it doesn't throw a column is ambiguous error (without renaming the alias)? Example:

Select coalesce(a.dob, b.birth_date) as dob

From db.table1 a

Join db.table2 b (on a.field1 = b.field1)

Where dob = '1900-01-01'  <--- throws an error because it doesn't know if dob is coming from db.table1 or the aliased field that I want to alias dob.

This is my silly work around but wondered if there is a better solution:

Select q.dob2 as dob

From(

Select coalesce(a.dob, b.birth_date) as dob2

From db.table1 a

Join db.table2 b (on a.field1 = b.field1)

Where dob2 = '1900-01-01'

) q

2 REPLIES
Enthusiast

Re: aliased column is ambiguous in where clause

one other way:

Select coalesce(a.dob, b.birth_date) as dob

From db.table1 a

Join db.table2 b (on a.field1 = b.field1)

Where coalesce(a.dob, b.birth_date) = '1900-01-01' 

Fan

Re: aliased column is ambiguous in where clause

Thanks Harpreet. It's the obvious answers that are sometimes the most difficult to realize. :)