I would like to know how to get all the duplicate values using the following query.please let me know what i am doing wrong with the query.
regards, Iftekhar
SQL> desc tmp_emp_Area
Name Null? Type
------------------------------- -------- ----
SC_CD VARCHAR2(2)
DIST_CD VARCHAR2(2)
THA_CD VARCHAR2(2)
UN_CD VARCHAR2(3)
FP_ID VARCHAR2(4)
S_DT DATE
END_DT DATE
PERFORM VARCHAR2(1)
BS_CD VARCHAR2(4)
MKT_CD VARCHAR2(3)
Query :
SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
FROM tmp_emp_area
WHERE rn >1
SQL> / WHERE rn >1 * ERROR at line 4: ORA-00904: "RN": invalid identifier
Please check these two results below that i got after running two different queries to get duplicates.one is showing 92 columns and other is showing 96 columns.
select SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,count(fp_id)
from tmp_emp_area
group by SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID
having count(fp_id)>1
/
SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 022 5J85 2
14 61 02 098 5J85 3
14 64 02 004 5J85 2
14 64 02 002 5J85 2
14 60 19 060 5F77 2
14 60 13 077 5F77 2
14 61 06 006 5D51 2
14 61 07 013 5D51 2
14 61 07 083 5D51 2
14 61 06 010 5D51 2
14 61 01 015 5R44 2
14 61 08 027 5R44 2
14 61 01 057 5R44 2
14 61 01 067 5R44 2
14 61 05 001 5R44 2
14 61 05 003 5R44 2
14 61 02 009 5J85 2
14 60 13 078 5F77 2
14 61 06 007 5D51 2
14 61 01 021 5R44 2
14 61 01 029 5R44 2
SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 01 065 5R44 2
14 61 01 069 5R44 2
14 64 03 013 5J85 2
14 61 02 014 5J85 2
14 61 02 089 5J85 2
14 60 19 132 5F77 2
14 60 19 134 5F77 2
14 61 07 086 5D51 2
14 61 06 035 5D51 2
14 61 06 014 5D51 2
14 61 01 031 5R44 2
14 61 01 036 5R44 2
14 61 01 041 5R44 2
14 61 02 092 5J85 3
14 61 02 074 5J85 3
14 61 02 088 5J85 2
14 61 02 109 5J85 2
14 60 19 014 5F77 2
14 61 07 015 5D51 2
14 61 06 008 5D51 2
14 61 06 016 5D51 2
SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 05 047 5R44 2
14 61 01 018 5R44 2
14 61 01 055 5R44 2
14 61 01 066 5R44 2
14 61 01 024 5R44 2
14 61 02 093 5J85 3
14 64 02 011 5J85 2
14 64 02 003 5J85 2
14 61 09 002 5J85 2
14 61 02 081 5J85 2
14 61 05 053 5D51 2
14 61 07 087 5D51 2
14 61 06 036 5D51 2
14 61 06 020 5D51 2
14 61 01 076 5R44 2
14 61 02 059 5R44 2
14 61 02 033 5J85 2
14 64 02 008 5J85 2
14 64 02 020 5J85 2
14 61 02 097 5J85 2
14 61 02 017 5J85 2
SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 082 5J85 2
14 61 01 077 5R44 2
14 61 05 046 5R44 2
14 61 01 017 5R44 2
14 61 01 054 5R44 2
14 64 02 030 5J85 2
14 61 02 010 5J85 2
14 61 02 103 5J85 2
14 64 02 006 5J85 2
14 64 03 020 5J85 2
14 61 02 105 5J85 2
14 61 02 080 5J85 2
14 61 02 151 5J85 2
14 60 19 059 5F77 2
14 61 06 045 5D51 2
14 61 01 075 5R44 2
14 61 01 056 5R44 2
14 61 01 020 5R44 2
14 61 05 007 5R44 2
14 61 01 053 5R44 2
14 61 01 078 5R44 2
SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 013 5J85 2
14 64 02 010 5J85 2
14 64 02 001 5J85 2
14 61 02 077 5J85 2
14 61 07 033 5D51 2
14 61 01 033 5R44 2
14 61 01 068 5R44 2
14 61 01 073 5R44 2
92 rows selected.
select *
from
(
SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
FROM tmp_emp_area
) dt
WHERE rn >1
SQL> /
SC DI TH UN_ FP_I RN
-- -- -- --- ---- ---------
14 60 13 077 5F77 2
14 60 13 078 5F77 2
14 60 19 014 5F77 2
14 60 19 059 5F77 2
14 60 19 060 5F77 2
14 60 19 132 5F77 2
14 60 19 134 5F77 2
14 61 01 015 5R44 2
14 61 01 017 5R44 2
14 61 01 018 5R44 2
14 61 01 020 5R44 2
14 61 01 021 5R44 2
14 61 01 024 5R44 2
14 61 01 029 5R44 2
14 61 01 031 5R44 2
14 61 01 033 5R44 2
14 61 01 036 5R44 2
14 61 01 041 5R44 2
14 61 01 053 5R44 2
14 61 01 054 5R44 2
14 61 01 055 5R44 2
SC DI TH UN_ FP_I RN
-- -- -- --- ---- ---------
14 61 01 056 5R44 2
14 61 01 057 5R44 2
14 61 01 065 5R44 2
14 61 01 066 5R44 2
14 61 01 067 5R44 2
14 61 01 068 5R44 2
14 61 01 069 5R44 2
14 61 01 073 5R44 2
14 61 01 075 5R44 2
14 61 01 076 5R44 2
14 61 01 077 5R44 2
14 61 01 078 5R44 2
14 61 02 009 5J85 2
14 61 02 010 5J85 2
14 61 02 013 5J85 2
14 61 02 014 5J85 2
14 61 02 017 5J85 2
14 61 02 022 5J85 2
14 61 02 033 5J85 2
14 61 02 059 5R44 2
14 61 02 074 5J85 2
SC DI TH UN_ FP_I RN
-- -- -- --- ---- ---------
14 61 02 074 5J85 3
14 61 02 077 5J85 2
14 61 02 080 5J85 2
14 61 02 081 5J85 2
14 61 02 082 5J85 2
14 61 02 088 5J85 2
14 61 02 089 5J85 2
14 61 02 092 5J85 2
14 61 02 092 5J85 3
14 61 02 093 5J85 2
14 61 02 093 5J85 3
14 61 02 097 5J85 2
14 61 02 098 5J85 2
14 61 02 098 5J85 3
14 61 02 103 5J85 2
14 61 02 105 5J85 2
14 61 02 109 5J85 2
14 61 02 151 5J85 2
14 61 05 001 5R44 2
14 61 05 003 5R44 2
14 61 05 007 5R44 2
SC DI TH UN_ FP_I RN
-- -- -- --- ---- ---------
14 61 05 046 5R44 2
14 61 05 047 5R44 2
14 61 05 053 5D51 2
14 61 06 006 5D51 2
14 61 06 007 5D51 2
14 61 06 008 5D51 2
14 61 06 010 5D51 2
14 61 06 014 5D51 2
14 61 06 016 5D51 2
14 61 06 020 5D51 2
14 61 06 035 5D51 2
14 61 06 036 5D51 2
14 61 06 045 5D51 2
14 61 07 013 5D51 2
14 61 07 015 5D51 2
14 61 07 033 5D51 2
14 61 07 083 5D51 2
14 61 07 086 5D51 2
14 61 07 087 5D51 2
14 61 08 027 5R44 2
14 61 09 002 5J85 2
SC DI TH UN_ FP_I RN
-- -- -- --- ---- ---------
14 64 02 001 5J85 2
14 64 02 002 5J85 2
14 64 02 003 5J85 2
14 64 02 004 5J85 2
14 64 02 006 5J85 2
14 64 02 008 5J85 2
14 64 02 010 5J85 2
14 64 02 011 5J85 2
14 64 02 020 5J85 2
14 64 02 030 5J85 2
14 64 03 013 5J85 2
14 64 03 020 5J85 2
96 rows selected.
You can't use an alias in WHERE, switch to a Derived Table: