[1]: ORA-00904: "VW_D"."CL_NAME": invalid identifier-CodePudding

I am trying to run this code in Oracle database but it's giving error :

 ORA-00904: "vw_d"."cl_name": invalid identifier

What's wrong with the query:

SELECT *
  FROM vw_doctrans vw_d
 WHERE (SELECT COUNT(*)
          FROM (SELECT *
                  FROM vw_doctrans vw
                 WHERE vw.cl_name = vw_d.cl_name
                 GROUP BY vw.country)) > 1

I tried this query in MySQL and works fine

CodePudding user response：

To me, it looks like

Sample data:

SQL> with vw_doctrans (id, cl_name, country) as
  2    (select 1, 'Bob', 'UK' from dual union all
  3     select 2, 'Bob', 'USA' from dual union all
  4     select 3, 'Bob', 'UAE' from dual union all
  5     select 4, 'Bob', 'UAE' from dual union all
  6     select 5, 'Bob', 'UAE' from dual union all
  7     --
  8     select 6, 'John', 'Canada' from dual union all
  9     select 7, 'John', 'Canada' from dual union all
 10     --
 11     select 8, 'Caroline', 'India' from dual union all
 12     select 9, 'Caroline', 'USA' from dual union all
 13     select 10, 'Caroline', 'USA' from dual union all
 14     select 11, 'Caroline', 'USA' from dual
 15    ),

Query begins here:

 16  temp as
 17    (select v.*,
 18            count(distinct country) over (partition by cl_name) cnt
 19     from vw_doctrans v
 20    )
 21  select distinct cl_name, country
 22  from temp
 23  where cnt >= 2
 24  order by cl_name, country;

CL_NAME  COUNTR
-------- ------
Bob      UAE
Bob      UK
Bob      USA
Caroline India
Caroline USA

SQL>

CodePudding user response：

You are way overthinking it. This is just:

SELECT *
FROM vw_doctrans vw_d
WHERE EXISTS (
    SELECT *
    FROM vw_doctrans vw
    WHERE vw.cl_name = vw_d.cl_name AND vw.country != vw_d.country
)

But your "I need also other columns too: Bob with countries, date, message of text. Not only cl_name and country column" directly conflicts with your "if message came from same country 2-3times show only one time the country." You can't have both; if you want only one row for each name/country combination, you need to decide how to pick which values for the other columns you want.