view raw
Odinovsky Odinovsky - 8 months ago 43
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
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


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
        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( else lower( end) like lower('%daily%') and
      r.report_status = 'Active'
order by name;