Oracle database column ambiguously defined / invalid identifier

Question

I've tried to write a SQL statement to select from some tables. But when I run it, I get an error, and I don't know how to fix it.

When I just select from vertrag and join pgrdat, abrkreis, mandant and komm_dat, everything works fine.

However, when I try to add literal into the joins, I get the following error:

ORA-00904: "VERTRAG"."MAN" invalid identifier
on (left join literal on literal.lit_kzl = pgrdat.beruftit and literal.man = vertrag.man)

or

ORA-00918: column ambiguously defined
on (left join Literal on Literal.LIT_KZL=pgrdat.BERUFTIT and Literal.man=man)

literal.man and vertrag.man both exist.

Here's my SQL:

SELECT man,
       ak,
       pnr,
       vertrag.vertnr,
       vertrag.eintrt2,
       vertrag.ma_ab,
       vertrag.verbegin,
       vertrag.ver_ab,
       vertrag.ver_bis,
       vertrag.verende,
       vertrag.enlogru,
       pgrdat.spr,
       pgrdat.anrede,
       pgrdat.auswnr,
       pgrdat.beruftit,
       pgrdat.daschudat,
       pgrdat.fax,
       pgrdat.gebdat,
       pgrdat.gebname,
       pgrdat.gebort,
       pgrdat.geschl,
       pgrdat.lnd,
       pgrdat.miname,
       pgrdat.namevor,
       pgrdat.namezus,
       pgrdat.naname,
       pgrdat.ort,
       pgrdat.plz,
       pgrdat.plzfach,
       pgrdat.postfach,
       pgrdat.pst_ab,
       pgrdat.pst_bis,
       pgrdat.staat,
       pgrdat.staat2,
       pgrdat.strasse,
       pgrdat.telgesch,
       pgrdat.telprivat,
       pgrdat.titel,
       pgrdat.vorname,
       pgrdat.empfaenger,
       pgrdat.taetint,
       pgrdat.zimmer,
       pgrdat.sachbegrp,
       pgrdat.logasach,
       pgrdat.stellung,
       pgrdat.logasach2,
       pgrdat.sachbegrp2,
       abrkreis.ak_bez,
       abrkreis.ak_kurz,
       abrkreis.ak_ort,
       abrkreis.ak_plz,
       abrkreis.ak_fax,
       abrkreis.ak_plzfach,
       abrkreis.ak_postfach,
       abrkreis.ak_strasse,
       abrkreis.ak_telefon,
       abrkreis.ak_text,
       mandant.man_bez,
       mandant.man_fax,
       mandant.man_firma,
       mandant.man_kurz,
       mandant.man_ort,
       mandant.man_plzfach,
       mandant.man_plzstr,
       mandant.man_postfach,
       mandant.man_st_nr,
       mandant.man_strasse,
       mandant.man_telefon,
       komm_dat.km_art,
       komm_dat.km_ab,
       komm_dat.km_bis,
       komm_dat.km_bem,
       literal.lit_txt
FROM   vertrag
JOIN   pgrdat
USING  (man, ak, pnr)
JOIN   abrkreis
USING  (man, ak)
JOIN   mandant
USING  (man)
LEFT   JOIN komm_dat
USING  (man, ak, pnr)
LEFT   JOIN literal
ON     literal.lit_kzl = pgrdat.beruftit
AND    literal.man = man
WHERE  superman IN ('41900', '41901', '41902', '41903')
AND    literal.lit_art = 'BERUFTIT'
AND    ver_ab <= trunc(SYSDATE)
AND    pgrdat.pst_ab <= trunc(SYSDATE)
AND    ((ver_bis >= trunc(SYSDATE) AND pgrdat.pst_bis >= trunc(SYSDATE)) OR (ver_bis IS NULL AND pgrdat.pst_bis IS NULL));

Answer 1

The problem is with mixing using and on join syntax. There is a third variant you didn't try:

left join Literal on Literal.LIT_KZL=pgrdat.BERUFTIT and Literal.man=komm_dat.man

which gets

ORA-25154: column part of USING clause cannot have qualifier

You can't use the unqualified man because it's ambiguous (appearing in several tables); you can't use a qualifier because it's been used in earlier using() clauses.

If it was an inner join you could change that Literal join to using (man) and move the lit_kzl check to the where clause, but as it's an outer join that won't work (or at least, would force it back to being an inner join).

You probably need to change all the other using clauses to on, unfortunately:

...
from vertrag
join pgrdat on pgrdat.man = vertrag.man and pgrdat.ak = vertrag.ak and pgrdat.pnr = vertrag.pnr
join abrkreis on abrkreis.man = vertrag.man and abrkreis.ak = vertrag.ak
join mandant on mandant.man = vertrag.man
left join komm_dat on komm_dat.man = vertrag.man and komm_dat.ak = vertrag.ak and komm_dat.pnr = vertrag.pnr
left join Literal on Literal.LIT_KZL=pgrdat.BERUFTIT and Literal.man=vertrag.man
and Literal.LIT_ART='BERUFTIT'
where ...

I've moved and Literal.LIT_ART='BERUFTIT' from the where clause into the join condition, as that would also have forced that outer join to become an inner join again.