How to select records from Duplicate Rows along with non Duplicate rows
Hello
I am working on Oracle with following details
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
I need help to select one of the duplicate records and some non duplicate based on the condition. I wrote the script which only gives me one of the record from duplicate list but I am confused how to select non duplicate with duplicate.
My condition is to select all the records where TARGETTABLE AND TARGET_COLUMN IS NOT NULL. I need to select unique records out of this this condition based on these tywo attributes only._
SOURCE_
SRC_COLUMN TARGET_TABLE TARGET_COLUMN
-----
--------------------
-----
SUBJECT_AREA SRC_EXTERNAL SUBJECT_AREA
FISCAL_YEAR SRC_EXTERNAL FISCAL_YEAR
PARTY_ID_ORG PARTY_ALT_ID PARTY_ID
PARTY_ID_NEW PARTY_ALT_ID PARTY_ID
ESTABLISHMENT_NBR_ORG W2_EMPLOYER ESTABLISHMENT_NBR
ESTABLISHMENT_NBR_NEW W2_EMPLOYER ESTABLISHMENT_NBR
EMPLOYMENT_CODE_ORG W2_EMPLOYER EMPLOYMENT_CODE
EMPLOYMENT_CODE_NEW W2_EMPLOYER EMPLOYMENT_CODE
SICK_PAY_IND_ORG W2_EMPLOYER SICK_PAY_IND
SICK_PAY_IND_NEW W2_EMPLOYER SICK_PAY_IND
AGENT_IND_CODE
AGENT_FEIN
EMPLOYER_NAME
EMPLOYER_ADDRESS
CLIENT_ADDRESS
CITY
STATE
ZIP
REQUIRED TARGET_
SRC_COLUMN TARGET_TABLE TARGET_COLUMN
-----
--------------------
-----
SICK_PAY_IND_NEW W2_EMPLOYER SICK_PAY_IND
EMPLOYMENT_CODE_NEW W2_EMPLOYER EMPLOYMENT_CODE
ESTABLISHMENT_NBR_NEW W2_EMPLOYER ESTABLISHMENT_NBR
PARTY_ID_NEW PARTY_ALT_ID PARTY_ID
SUBJECT_AREA SRC_EXTERNAL SUBJECT_AREA
FISCAL_YEAR SRC_EXTERNAL FISCAL_YEAR
MY QUERY_+
SELECT SRC_FILENAME,SRC_COLUMN,TARGET_TABLE, TARGET_COLUMN
FROM ITS_STAGE.STG_EDW_METADATA X
WHERE TARGET_TABLE IS NOT NULL AND TARGET_COLUMN IS NOT NULL
AND ROWID > (SELECT MIN(ROWID)
FROM ITS_STAGE.STG_EDW_METADATA Y
WHERE TARGET_TABLE IS NOT NULL AND TARGET_COLUMN IS NOT NULL
AND Y.TARGET_TABLE=X.TARGET_TABLE
AND Y.TARGET_COLUMN=X.TARGET_COLUMN)
MY RESULTS+_
SRC_COLUMN TARGET_TABLE TARGET_COLUMN
-----
--------------------
-----
SICK_PAY_IND_NEW W2_EMPLOYER SICK_PAY_IND
EMPLOYMENT_CODE_NEW W2_EMPLOYER EMPLOYMENT_CODE
ESTABLISHMENT_NBR_NEW W2_EMPLOYER ESTABLISHMENT_NBR
PARTY_ID_NEW PARTY_ALT_ID PARTY_ID
Can some Oracle expert please help me solving this issue.
Thanks in Advance
Rajesh
Edited by: user532468 on Sep 15, 2008 2:32 PM