Home » SQL & PL/SQL » SQL & PL/SQL » Find duplicate rows having same null values (10g)
| Find duplicate rows having same null values [message #628683] |
Thu, 27 November 2014 00:55  |
 |
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
need help, please
i have a case that i have to find duplicate rows having same null values
the duplication is based on ID_Regis, Birt_date, Place_date
this is the case
ID NAME ID_Regis BIRTH_DATE PLACE DATE
---------------------------------------------------------------------------------------------
1 |xxx |04/02.040.607/102/552
2 |yyy |12.345/6.78/9 |28/12/1972 |Tokyo
3 |xxx |04.02/040/607.102.552
this is the result expected
ID NAME ID_Regis BIRTH_DATE PLACE DATE
------------------------------------------------------------------------------------------
1 |xxx |0402040607102552
3 |xxx |0402040607102552
this is mu query
SELECT ID, NAME ,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client a
where DATE_BIRTH = NULL
and PLACE_BIRTH = NULL
and EXISTS
(SELECT ID, NAME,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client
where NAME = a.NAME
AND ID_regis = a.ID_regis
AND DATE_BIRTH = a.DATE_BIRTH
and PLACE_BIRTH = a.PLACE_BIRTH
AND ROWID < a.ROWID)
order by ID
this got no error but shows no data.
this is my query
hope there is somebody can help me, big thanks
|
|
|
|
|
|
| Re: Find duplicate rows having same null values [message #628686 is a reply to message #628684] |
Thu, 27 November 2014 01:14  |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
thanks for your info how to make a good post in this forum 
Quote:You have to use IS NULL operator to test if a column "contains" NULL
SELECT ID, NAME ,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client a
where DATE_BIRTH is NULL
and PLACE_BIRTH is NULL
and EXISTS
(SELECT ID, NAME,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client
where NAME = a.NAME
AND ID_regis = a.ID_regis
AND DATE_BIRTH = a.DATE_BIRTH
and PLACE_BIRTH = a.PLACE_BIRTH
AND ROWID < a.ROWID)
order by cifno
it doesnt work out
i still got no data selected
is there any way out else?
[Updated on: Thu, 27 November 2014 01:15] Report message to a moderator |
|
|
|
|
|
| Re: Find duplicate rows having same null values [message #628691 is a reply to message #628686] |
Thu, 27 November 2014 01:36 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your outer query is selecting rows
where DATE_BIRTH is NULL
and PLACE_BIRTH is NULL
OK so far, but then in the inner query you have
AND DATE_BIRTH = a.DATE_BIRTH
and PLACE_BIRTH = a.PLACE_BIRTH
which is never true if the columns are null. So you'll never get anything back. Use IS NULL as the test there, too.
|
|
|
|
| Re: Find duplicate rows having same null values [message #628692 is a reply to message #628687] |
Thu, 27 November 2014 01:43 |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
i have a table named client
this is the value of table client
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
1 Jhon 04/02.040.607/102/552
2 SAM 12.345/6.78/9 28/12/1972 Tokyo
3 Jhon 04.02/040/607.102.552
i have to get rows having field birth_date is null, place_date is null and have same ID_regis number evenif there's character in the field, so i have to use REPLACE to make them same.
this is the result expected
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
1 Jhon 0402040607102552
3 Jhon 0402040607102552
i use this query
SELECT ID, NAME ,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client a
where DATE_BIRTH is NULL
and PLACE_BIRTH is NULL
and EXISTS
(SELECT ID, NAME,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client
where NAME = a.NAME
AND ID_regis = a.ID_regis
AND DATE_BIRTH = a.DATE_BIRTH
and PLACE_BIRTH = a.PLACE_BIRTH
AND ROWID < a.ROWID)
order by cifno
but it doesnt work,
it got no error but i got no data like this
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
Quote:
Post a test case.
is that a correct test case?
|
|
|
|
| Re: Find duplicate rows having same null values [message #628694 is a reply to message #628692] |
Thu, 27 November 2014 01:47 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Thu, 27 November 2014 08:02...
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.
...
Does your post match this sentence?
Did you read the link?
INSERT statements for the data are also required.
[Updated on: Thu, 27 November 2014 01:48] Report message to a moderator |
|
|
|
| Re: Find duplicate rows having same null values [message #628695 is a reply to message #628691] |
Thu, 27 November 2014 01:51 |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
Quote:
which is never true if the columns are null. So you'll never get anything back. Use IS NULL as the test there, too.
SELECT ID, NAME ,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client a
where DATE_BIRTH is NULL
and PLACE_BIRTH is NULL
and EXISTS
(SELECT ID, NAME,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM zg20client b
where DATE_BIRTH is NULL
AND PLACE_BIRTH is NULL
AND b.NAME = a.NAME
AND b.ID_regis = a.ID_regis
AND b.DATE_BIRTH = a.DATE_BIRTH
and b.PLACE_BIRTH = a.PLACE_BIRTH
AND ROWID < a.ROWID)
order by ID
it still doesnt make it, sir
[Updated on: Thu, 27 November 2014 01:52] Report message to a moderator |
|
|
|
|
|
| Re: Find duplicate rows having same null values [message #628699 is a reply to message #628696] |
Thu, 27 November 2014 02:33 |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
i have a table named client
create table client(
ID varchar2(10),
Name varchar2(50),
ID_regis varchar2(50),
Birth_date date,
Place_date varchar2(50)
)
insert into client values ('1','Jhon','04/02.040.607/102/552','','')
insert into client values ('2','SAM','12.345/6.78/9 ','28/DEC/1972','Tokyo')
insert into client values ('3','Jhon','04.02/040/607.102.552','','')
this is the value of table client
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
1 Jhon 04/02.040.607/102/552
2 SAM 12.345/6.78/9 28/12/1972 Tokyo
3 Jhon 04.02/040/607.102.552
i have to get rows having field birth_date is null, place_date is null and have same ID_regis number evenif there's character in the field, so i have to use REPLACE to make them same.
the duplication is based on ID_Regis, Birt_date, Place_date
this is the result expected
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
1 Jhon 0402040607102552
3 Jhon 0402040607102552
i use this query
SELECT ID, NAME ,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM client a
where DATE_BIRTH is NULL
and PLACE_BIRTH is NULL
and EXISTS
(SELECT ID, NAME,replace(replace(ID_regis,'.',''),'/','') as idno ,DATE_BIRTH,PLACE_BIRTH
FROM client b
where DATE_BIRTH is NULL
AND PLACE_BIRTH is NULL
AND b.NAME = a.NAME
AND b.ID_regis = a.ID_regis
AND b.DATE_BIRTH = a.DATE_BIRTH
and b.PLACE_BIRTH = a.PLACE_BIRTH
AND ROWID < a.ROWID)
order by ID
but it doesnt work,
it got no error but i got no data like this
ID Name ID_Regis Birth_date Place_date
----------------------------------------------------------------------------------------
Quote:
Post a test case.
is that a correct test case ? include create and insert statements
[Updated on: Thu, 27 November 2014 02:45] Report message to a moderator |
|
|
|
| Re: Find duplicate rows having same null values [message #628701 is a reply to message #628695] |
Thu, 27 November 2014 03:09 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
nciteamo wrote on Thu, 27 November 2014 07:51<snip>
it still doesnt make it, sir
Of course it doesn't work: you are still comparing null with null. Don't do that. Just adding the correct condition is not enough: you need to remove the wrong one.
|
|
|
|
| Re: Find duplicate rows having same null values [message #628732 is a reply to message #628699] |
Thu, 27 November 2014 10:11 |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
|
As John said you still say that date birth and place birth should be equal, and you've already told it that it should also be null. Another thing is that when you compare id_regis in the where clause of the exists part of the query you no longer use the replace. So they won't be equal.
|
|
|
|
|
|
|
|
| Re: Find duplicate rows having same null values [message #628747 is a reply to message #628746] |
Thu, 27 November 2014 21:51 |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
yes i got it
this is the query
select * from (select id,name,replace(replace(id_regis,'.',''),'/','') id_regis,birth_date,place_date from client
where birth_date is null
and place_date is null) a
where exists (select 1 from
(select replace(replace(id_regis,'.',''),'/','') id_regis from client)
where id_regis = a.id_regis
and rowid > a.rowid)
thanks all
|
|
|
|
| Re: Find duplicate rows having same null values [message #628753 is a reply to message #628746] |
Fri, 28 November 2014 03:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Fri, 28 November 2014 03:17
WHEN NULL = NULL -- is always false
It's never false, it's always null, and yes the distinction matters:
SQL> SELECT 1 FROM dual WHERE 1=2;
no rows selected
SQL> SELECT 1 FROM dual WHERE NOT 1=2;
1
----------
1
SQL> SELECT 1 FROM dual WHERE NULL=NULL;
no rows selected
SQL> SELECT 1 FROM dual WHERE NOT NULL=NULL;
no rows selected
SQL>
[Updated on: Fri, 28 November 2014 03:29] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:38:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
