Odinovsky Odinovsky - 1 year ago 197
SQL Question

Column reference 'name' is ambiguous

I already read from previous answers here in SO that using an alias in a where statement isn't possible. But I'm wondering how this query can be re-written?

WHEN app_reports_mgmt.reports.rid = app_reports_mgmt.report_template.rid THEN
ELSE app_reports_mgmt.reports.name
END AS name
FROM app_reports_mgmt.reports left join app_reports_mgmt.report_template on app_reports_mgmt.reports.rid = app_reports_mgmt.report_template.rid
where LOWER(name) LIKE LOWER('%daily%') and app_reports_mgmt.reports.report_status = 'Active' order by name

Answer Source

After some investigation, the problem is in the where clause, not the order by.

You could fix this by referencing by position:

select (case when r.rid = t.rid
             then t.name
             else r.name   
        end) as name
from app_reports_mgmt.reports r left join
     app_reports_mgmt.report_template t
     on r.rid = t.rid
where (case when r.rid = t.rid then lower(t.name) else lower(r.name) end) like lower('%daily%') and
      r.report_status = 'Active'
order by name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download