0

In the code below I get an error from the WHERE clause in the subquery. It sais "column ambiguously defined". I don't understand how any column can be ambiguously defined because everytime I specify from which table the column originates.

WITH everything AS
(
 SELECT *
   FROM hvc_hvvisitresult a, hvc_am0 b, hvc_visitresultdetails c, hvc_ar0 d
  WHERE a.am0_nrid = b.am0_nrid
    AND a.hvvisitresult_nrid = c.hvvisitresult_nrid
    AND c.ar0_nrid = d.ar0_nrid
    AND hvoutcome_nrid = 2
    AND TO_CHAR(dateonly, 'yyyy') = 2008
    AND TO_CHAR(dateonly, 'mm') = 1
)
SELECT DISTINCT description
  FROM everything a
 WHERE EXISTS (SELECT *
                 FROM everything b
                WHERE a.ar0_nrid <> b.ar0_nrid
                  AND a.am0_nrid = b.am0_nrid
                  AND a.so0_nrid = b.so0_nrid
                  AND a.dateonly = b.dateonly)

Could someone help me please?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Arthur Glppn
  • 1
  • 2
    Remove * and use full column names, along with aliases when necessary (so that some of them aren't duplicated and thus being ambiguously defined when referred in outer query). – Littlefoot Nov 17 '22 at 13:34
  • 1
    Also, please don't use SQL syntax from 1990 and use proper ANSI join syntax. – VvdL Nov 17 '22 at 13:37
  • You have columns with the same name in different tables; you can see that from the join condition where `am0_nrid` is in both `a` and `b`, `hvvisitresult_nrid` is in `a` and `c`, etc. Rather than using `SELECT *`, you need to ether only include one of them in the `SELECT` clause or if you do need to include them all then you will need to give one of the columns a different alias. – MT0 Nov 17 '22 at 13:43

0 Answers0