user843796 user843796 - 2 months ago 10
SQL Question

Oracle SQL left outer join

I am trying to take two queries and join the reults of query 2 (q2) to query 1 (q1) where usid match on both tables. I am getting the error: ORA-00904: "Q2"."ENODEB_ID": invalid identifier.

SELECT
q1.Date_Range, q1.usid, q2.enodeb_id
FROM
(SELECT to_char(to_date(PERIODSTART,'DD/MM/YYYY'), 'MM-DD') || '-' || to_char(to_date(PERIODEND,'DD/MM/YYYY'), 'MM-DD') as Date_Range,
usid FROM CQI_DASH.REGIONAL_LCQI_TOPOFFEND) q1
LEFT JOIN
(select usid, LISTAGG(ENODEB_ID, ',') WITHIN GROUP (ORDER BY ENODEB_ID)
FROM (
select usid,
ENODEB_ID,
row_number() over (partition by usid, ENODEB_ID order by usid) as rn
from AXIOM.NRS_LTE_ALU_CELLS
order by usid, ENODEB_ID)
WHERE rn = 1
GROUP BY usid) q2
ON q1.usid = q2.usid;

Answer

In your outer query you select q2.enodeb_id.

However, in the definition of q2 you only select a LISTAGG, and you don't give it an alias. Did you mean to call it enodeb_id?

Comments