Sql Subselect ORA-00918

This is my Sql:

SELECT 
    t.RUFNAME,
    count(v_anschrift.rufname) as Anrufe
FROM v_termin,
(
    SELECT 
    RUFNAME
    FROM
    V_Anschrift
    WHERE V_ANSCHRIFT.SPERRKZ = 0
    AND V_ANSCHRIFT.OEM != 0 
) t
JOIN v_anschrift ON (t.Rufname = v_anschrift.RUFNAME)
LEFT JOIN v_termin ON (v_termin.anschriftid = v_anschrift.anschriftid)
WHERE v_termin.TERMINART = 20
GROUP BY t.RUFNAME;

And i get this error message:

ORA-00918: Spalte nicht eindeutig definiert 00918. 00000 – “column ambiguously defined” *Cause:
*Action: Fehler in Zeile: 15 Spalte: 7

where is my mistake?

Answer

I suspect you actually want something like this:

SELECT t.RUFNAME, COUNT(*) as  Anrufe
FROM v_termin t JOIN
     v_anschrift a
     ON t.anschriftid = a.anschriftid
WHERE t.TERMINART = 20 AND
      a.SPERRKZ = 0 AND
      a.OEM <> 0 
GROUP BY t.RUFNAME;

You have repeated table references in the FROM clause — and the repetitions have no obvious logic.

Advertisement

Answer