0

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);
user3503711
  • 1,623
  • 1
  • 21
  • 32
39Clues
  • 1
  • 1
  • Please [edit] your post so that it's not just a code dump, and make an effort to format the code so that it's readable. You can get formatting help by clicking the `?` icon at the top right of the post editor toolbar. – Ken White Jun 11 '21 at 00:13

2 Answers2

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 ;
Amit Verma
  • 2,450
  • 2
  • 8
  • 21
  • Getting this error "ORA-00904: "DUPES"."COULMN_NAME": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 5 Column: 20" – 39Clues Jun 14 '21 at 23:49
0

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;
monika
  • 67
  • 7