SQL command not properly ended - errors in SQL query

Question

can anyone please help as I am getting an error:

"SQL command not properly ended"

Below is the SQL code:

SELECT 
c.column_name,
c.Table_name
FROM ALL_TAB_COLUMNS c
WHERE UPPER(COLUMN_NAME) LIKE '%xyt%'
AND OWNER NOT LIKE '%ytr%'
AND OWNER <> 'SYS'
ORDER BY 1,2,3
INNER JOIN
(SELECT column_name
FROM ALL_TAB_COLUMNS
GROUP BY column_Name
HAVING count(*)>1
)dupes
ON (dupes.coulmn_name = c.column_name);

Answer 1

joins should be used before where clause. you have only 2 columns in select therefore order by will have only 1,2. your query should be like this:

SELECT c.column_name, c.Table_name 
FROM ALL_TAB_COLUMNS c 
inner join (
            select column_name FROM ALL_TAB_COLUMNS group by column_Name having count(*)>1 
        )dupes on (dupes.coulmn_name = c.column_name)
WHERE UPPER(c.COLUMN_NAME) LIKE '%xyt%' AND OWNER NOT LIKE '%ytr%' AND OWNER <> 'SYS' 
ORDER BY 1,2 ;

Answer 2

You can use below query to get same output:

SELECT c.column_name, c.Table_name 
FROM ALL_TAB_COLUMNS c ,( select b.column_name FROM ALL_TAB_COLUMNS b group by b.column_Name having count(*)>1 ) d
WHERE d.column_name = c.column_name
and UPPER(c.COLUMN_NAME) LIKE '%xyt%' 
AND OWNER NOT LIKE '%ytr%' 
AND OWNER <> 'SYS' 
ORDER BY 1,2;