Hi,
Oracle version details:
----------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
"CORE 12.2.0.1.0 Production" 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
how to get duplicate records in a view.
I am running below query where AST_BASEELEMENT is a view. the view is getting data from multiple tables.
I was asked to give the report what are duplicate records in the view based on given set of columns.
SELECT RECONCILIATION_IDENTITY,
INSTANCE_ID,
ASSET_ID_,
NAME,
DATA_SET_ID,
CLASS_ID
FROM AST_BASEELEMENT
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM AST_BASEELEMENT
GROUP BY RECONCILIATION_IDENTITY,
INSTANCE_ID,
ASSET_ID_,
NAME,
DATA_SET_ID,
CLASS_ID
);
i am getting below error :
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
01445. 00000 - "cannot select ROWID from, or sample, a join view without a key-preserved table"
*Cause:
*Action:
Error at Line: 97 Column: 6
I understood that ROWID cannot be used on view, can be used only on tables.
here do we have any other option that without using ROWID can we pull duplicate records?
note: the view is having a unique column but it is a string, let me know if that helps.
Thanks.