Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Address
Duplicate Address [message #284662] Fri, 30 November 2007 07:59 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,

In emp table the records are like below


NAME       PLACE             AGE        SAL      EMPNO
---------- ---------- ---------- ---------- ----------
venkat     INDIA              32       1000      23322
venkat     INDIA              32       2332       2333
POLARIS    Pakis              23       2332      23343
POLARIS    Pakis              23     233233      23343
ashok      argen              25      33333      33333


I want to take the all records from the above table whose name or place will be same and here we should not use the primary key column value

Can any one guide me.

[Updated on: Fri, 30 November 2007 08:00] by Moderator

Report message to a moderator

Re: Duplicate Address [message #284664 is a reply to message #284662] Fri, 30 November 2007 08:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And where does Forms get involved?

MHE
Re: Duplicate Address [message #284668 is a reply to message #284664] Fri, 30 November 2007 08:18 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,
I am not getting you can u please explain brief
Re: Duplicate Address [message #284705 is a reply to message #284668] Fri, 30 November 2007 11:12 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Mahee,

Even we are not able to get the records using primary key because for same value's record having different primary key value (empno).

Can you give me a way to get the record.

ASK
Re: Duplicate Address [message #284723 is a reply to message #284705] Fri, 30 November 2007 12:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does this problem have to do with Forms Builder? If it does, could you explain it, please?

Saying that you "want to take the all records from the above table" - what does it mean? Do you want to select duplicate records? Do you want to delete duplicate records from the table? What would be the result of this "operation" (whichever it is) (extract those records and post them, just as you did in the first message. Please, use [code] tags so that we could easily read desired output).
Re: Duplicate Address [message #285071 is a reply to message #284723] Mon, 03 December 2007 05:17 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Here there is no involvement of forms.

The data i am given earlier is the records in the table and i want to take the records whose name or place values are same

For example : with the below data

NAME PLACE AGE SAL EMPNO
---------- ---------- ---------- ---------- ----------
venkat INDIA 32 1000 23322
venkat INDIA 32 2332 2333
POLARIS Pakis 23 2332 23343
POLARIS Pakis 23 233233 23343
ashok argen 25 33333 33333

i want to take the records like the below

NAME PLACE AGE SAL EMPNO
---------- ---------- ---------- ---------- ----------
venkat INDIA 32 1000 23322
venkat INDIA 32 2332 2333
POLARIS Pakis 23 2332 23343
POLARIS Pakis 23 233233 23343

whose name and place values are same for that i am using the below query

select a.empno,a.name,a.place,a.age,a.sal
from t1 a,t1 b
where a.name=b.name or a.place=b.place or a.age=b.age or a.sal=b.sal or a.empno=b.empno
group by a.name,a.place,a.age,a.sal,a.empno
having count(*)>1
order by a.empno,a.name,a.place,a.age,a.sal


confirm whether i am used correct or not why am i asking this is when any one or more where clause values can be a NULL value for that how my query will work correct or not




Re: Duplicate Address [message #285080 is a reply to message #285071] Mon, 03 December 2007 05:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LF

Please, use [code] tags so that we could easily read desired output).
Is there a special reason for you not to use [code] tags (as requested and suggested in the OraFAQ Forum Guide)?

Oragenashok

... when any one or more where clause values can be a NULL value ...
In that case, use NVL function.
Re: Duplicate Address [message #285215 is a reply to message #285080] Mon, 03 December 2007 23:53 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
  1  select  NVL(a.f_name||a.l_name,' '),
  2   NVL(a.add_1||a.add_2||a.add_3||a.add_4,' '),
  3   NVL(a.d_o_b,' '),
  4   NVL(a.dr_lc_no,' '),
  5   NVL(a.pass_no,' '),
  6   NVL(a.email_id,' ')
  7  from t1 a,t1 b
  8  where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
  9  ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
 10   a.d_o_b=b.d_o_b or
 11   a.dr_lc_no=b.dr_lc_no or
 12   a.pass_no=b.pass_no or
 13   a.email_id=b.email_id
 14  group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no,a.email_id
 15  having count(*)>1
 16* order by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no,a.email_id
SQL> /
select  NVL(a.f_name||a.l_name,' '),
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP



The above is my query any i got the error,but the count of same column value record as i told earlier is 3382 records but my query will return some cartesian product.

Can you verify my query.Its very urgent
Re: Duplicate Address [message #285217 is a reply to message #284662] Tue, 04 December 2007 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can you verify my query.Its very urgent
Yes, you have a query.

Please explain why it is URGENT for ME to solve this problem for YOU at the expense of my time & knowledge at NO cost to you?

[Updated on: Tue, 04 December 2007 00:00] by Moderator

Report message to a moderator

Re: Duplicate Address [message #285221 is a reply to message #285217] Tue, 04 December 2007 00:15 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

@ORAGENASHOK you are unnecessarily making things complicated.what you need is to check the existence of person having same NAME & ADDRESS repeated in your table.

hint :
select column_names ..where_clause (col1,col2)
in (select col1,col2 from ..group_by_clause .. having_clause)


regards,
Re: Duplicate Address [message #285239 is a reply to message #285221] Tue, 04 December 2007 01:17 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Dhanjai,

I replaced with your format,but it shows the same error.


  1  select f_name||l_name,
  2  add_1||add_2||add_3||add_4,
  3  d_o_b,
  4  dr_lc_no,
  5  pass_no,
  6  email_id
  7  from gr010mb
  8  where (f_name||l_name,
  9  add_1||add_2||add_3||add_4,
 10  d_o_b,
 11  dr_lc_no,
 12  pass_no,
 13  email_id)
 14  in (select  a.f_name||a.l_name,
 15  a.add_1||a.add_2||a.add_3||a.add_4,
 16  a.d_o_b,
 17  a.dr_lc_no,
 18  a.pass_no,
 19  a.email_id
 20  from gr010mb a,gr010mb b
 21  where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
 22  ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
 23  a.d_o_b=b.d_o_b or
 24  a.dr_lc_no=b.dr_lc_no or
 25  a.pass_no=b.pass_no or
 26  a.email_id=b.email_id
 27  group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no,a.email_id
 28  having count(*)>1
 29* )
SQL> /
from gr010mb
     *
ERROR at line 7:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


Re: Duplicate Address [message #285241 is a reply to message #285239] Tue, 04 December 2007 01:19 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Did you search for above ORA-error on net or forms.

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Re: Duplicate Address [message #285243 is a reply to message #285241] Tue, 04 December 2007 01:27 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
You mean my query is correct but there is some problem with DB in segment allocation.
Re: Duplicate Address [message #285251 is a reply to message #285243] Tue, 04 December 2007 01:39 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Again Same answer.
Did you fix ORA-01652 error. ?
Re: Duplicate Address [message #285253 is a reply to message #284662] Tue, 04 December 2007 02:12 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Your temporary tablespace is empty in that it has no free space left to carry out the operation.
conenct as a privileged user and issue the following command:

alter tablespace temp add tempfile 'location of tempfile' size "your desired size in K/M";
Re: Duplicate Address [message #285254 is a reply to message #285243] Tue, 04 December 2007 02:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Your query may or may not be correct.
But primary concern with your query right now regarding the temporary tablespace storage space. Add datafiles to that tablespace first.

And i am just wondering what you are trying to do with your complex Subquery ? why not using simple suqery or WHERE Exist clause ?


Thumbs Up
Rajuvan
Re: Duplicate Address [message #285255 is a reply to message #285253] Tue, 04 December 2007 02:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Maybe we should ask whether ORAGENASHOK has the authority to perform such (DBA) activities Wink

If not: I would advise to seek help from the company's DBA
Re: Duplicate Address [message #285336 is a reply to message #285255] Tue, 04 December 2007 04:51 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Yes i am not having authority to perform such DBA's activity
Re: Duplicate Address [message #285337 is a reply to message #285336] Tue, 04 December 2007 04:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
ORAGENASHOK wrote on Tue, 04 December 2007 11:51

Yes i am not having authority to perform such DBA's activity


Then you know what to do:

MarcS wrote on Tue, 04 December 2007 09:17


If not: I would advise to seek help from the company's DBA

Re: Duplicate Address [message #285346 is a reply to message #285336] Tue, 04 December 2007 05:05 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Even i am having another environment in that it is working fine.


with the below query


select  a.f_name||a.l_name,
a.add_1||a.add_2||a.add_3||a.add_4,
a.d_o_b,
a.dr_lc_no,
a.pass_no,
a.email_id
from t1 a,t1 b
where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
a.d_o_b=b.d_o_b or
a.dr_lc_no=b.dr_lc_no or
a.pass_no=b.pass_no or
a.email_id=b.email_id
group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no,a.email_id
having count(*)>1


when i am using the another query in below it shows no data found

select f_name||l_name,
add_1||add_2||add_3||add_4,
d_o_b,
dr_lc_no,
pass_no,
email_id 
from t1
where (f_name||l_name,
add_1||add_2||add_3||add_4,
d_o_b,
dr_lc_no,
pass_no,
email_id)
in (select  a.f_name||a.l_name,
a.add_1||a.add_2||a.add_3||a.add_4,
a.d_o_b,
a.dr_lc_no,
a.pass_no,
a.email_id
from t1 a,t1 b
where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
a.d_o_b=b.d_o_b or
a.dr_lc_no=b.dr_lc_no or
a.pass_no=b.pass_no or
a.email_id=b.email_id
group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no,a.email_id
having count(*)>1
)





Re: Duplicate Address [message #285356 is a reply to message #285346] Tue, 04 December 2007 05:19 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Any one please help me to extract the correct records.

Once again i will remain the concept.
should take the records whose name or address or place or age or DOB or email_id's are are not all but any one of the given should be same for two or more records.





Re: Duplicate Address [message #285361 is a reply to message #284662] Tue, 04 December 2007 05:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you sure that first query returns some rows and second one returns no records ?

Thumbs Up
Rajuvan.

[Updated on: Tue, 04 December 2007 05:29]

Report message to a moderator

Re: Duplicate Address [message #285362 is a reply to message #285361] Tue, 04 December 2007 05:28 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Inner query return some rows
But very first query return no rows.
Re: Duplicate Address [message #285366 is a reply to message #284662] Tue, 04 December 2007 05:34 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ashok,
Can you post create table and insert table's scripts?

Kiran.
Re: Duplicate Address [message #285374 is a reply to message #284662] Tue, 04 December 2007 05:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

That mean to say there are no records that have all the field values same

...
WHERE
(f_name||l_name,
add_1||add_2||add_3||add_4,
d_o_b,
dr_lc_no,
pass_no,
email_id)
in (select  a.f_name||a.l_name,
a.add_1||a.add_2||a.add_3||a.add_4,
a.d_o_b,
a.dr_lc_no,
a.pass_no,
a.email_id
FROM ...


means ,

Quote:

f_name||l_name = a.f_name||a.l_name AND
add_1||add_2||add_3||add_4 = a.add_1||a.add_2||a.add_3||a.add_4
d_o_b = a.d_o_b AND ...




Thumbs Up
Rajuvan

[Updated on: Tue, 04 December 2007 05:45]

Report message to a moderator

Re: Duplicate Address [message #285463 is a reply to message #284662] Tue, 04 December 2007 14:46 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select name,place,age,sal,empno
from my_table
where (name,place) 
in
(select name,place
 from my_table
 group by name,place
 having count(*) > 1)
order by name,place;
Re: Duplicate Address [message #285502 is a reply to message #285356] Tue, 04 December 2007 22:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


ORAGENASHOK wrote on Tue, 04 December 2007 16:49

Any one please help me to extract the correct records.

Once again i will remain the concept.
should take the records whose name or address or place or age or DOB or email_id's are are not all but any one of the given should be same for two or more records.



Thumbs Up
Rajuvan
Re: Duplicate Address [message #285525 is a reply to message #285374] Wed, 05 December 2007 00:01 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
rajavu,

Can you please explain about the below mentioned posted by you i am not getting.


That mean to say there are no records that have all the field values same
Re: Duplicate Address [message #285527 is a reply to message #285463] Wed, 05 December 2007 00:05 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Bill

   select name,place,age,sal,empno
from my_table
where (name,place) 
in
(select name,place
 from my_table
 group by name,place
 having count(*) > 1)
order by name,place;



I can understand the query but i want to check the condition that name or place or age or sal or empno should be same with any of the another record in the same table (my_table).
Re: Duplicate Address [message #285529 is a reply to message #284662] Wed, 05 December 2007 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ORAGENASHOK,
which of the guidelines as stated below
http://www.orafaq.com/forum/t/88153/0/
have you actually done to facilitate getting your issue resolved?
Re: Duplicate Address [message #285534 is a reply to message #285529] Wed, 05 December 2007 00:13 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
anacedent,

Yes, i don't need to criticise the thing please help me to get the solution.
Re: Duplicate Address [message #285538 is a reply to message #284662] Wed, 05 December 2007 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)

Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.

Provide your expected result set and explain the rules/reasons that lead to it.

Re: Duplicate Address [message #285539 is a reply to message #284662] Wed, 05 December 2007 00:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ie,

select * from emp 
 		where (DEPTNO, DNAME ) in (SELECT DEPTNO, DNAME FROM DEPT )


means

Quote:

select * from emp e, Dept d
where e.DEPTNO = d.DEPTNO
AND e.DNAME = d.DNAME


Where you want query like

Quote:

select * from emp e, Dept d
where e.DEPTNO = d.DEPTNO
OR e.DNAME = d.DNAME



Thumbs Up
Rajuvan.

Re: Duplicate Address [message #285648 is a reply to message #285538] Wed, 05 December 2007 04:03 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
CREATE TABLE T1
(
  BCD                 VARCHAR2(6)          NOT NULL
  C_NO                CHAR(10)             NOT NULL
  F_NAME              VARCHAR2(20)
  L_NAME              VARCHAR2(20)
  ADD_1               VARCHAR2(35)
  ADD_2               VARCHAR2(35)
  ADD_3               VARCHAR2(35)
  ADD_4               VARCHAR2(35)
  D_O_B               DATE
  PASS_NO             VARCHAR2(200)
  DR_LC_NO            VARCHAR2(16)
)


Insert script :
=================
insert into t2 values('000604','912','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2','ADDRESS LINE 3',NULL,NULL,'123456',NULL);
insert into t2 values('000604','004','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2','ADDRESS LINE 3',NULL,NULL,NULL,NULL);
insert into t2 values('000604','913','FIRST NAME','LAST NAME','ADDRESS LINE 1',NULL,NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','914','FIRST NAME','LAST NAME','ADDRESS LINE 1',NULL,NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','213','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2',NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','865','FIRST NAME','LAST NAME','ADDRESS LINE 1',NULL,NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','041','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2',NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','107','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2','ADDRESS LINE 3','ADDRESS LINE 4',NULL,'123456',NULL);
insert into t2 values('000604','866','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2',NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','867','FIRST NAME','LAST NAME','ADDRESS LINE 1','ADDRESS LINE 2',NULL,NULL,NULL,NULL,NULL);
insert into t2 values('000604','1000','Ashok','Kumar','add_1', 'ADD_2','ADD_3','ADD_4','02-APR-1982','46465','852852855');



I want the want below records whose f_name or l_name or add_1 or add_2 are (any one not all)

BCD    C_NO       F_NAME               L_NAME               ADD_1                               ADD_2                   
------ ---------- -------------------- -------------------- ----------------------------------- -----------------
000604 213        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2
000604 214        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2
000604 912        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2          
000604 004        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2          
000604 913        FIRST NAME           LAST NAME            ADDRESS LINE 1
000604 914        FIRST NAME           LAST NAME            ADDRESS LINE 1
000604 213        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2
000604 865        FIRST NAME           LAST NAME            ADDRESS LINE 1
000604 041        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2
000604 107        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2          
000604 866        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2
000604 867        FIRST NAME           LAST NAME            ADDRESS LINE 1                      ADDRESS LINE 2


The below record is not meet my condition :

000604 1000       Ashok                Kumar                add_1                               ADD_2   



Frame query for the result
Re: Duplicate Address [message #285649 is a reply to message #285648] Wed, 05 December 2007 04:04 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
In create table script change the T1 as T2
Re: Duplicate Address [message #285655 is a reply to message #284662] Wed, 05 December 2007 04:16 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SELECT *
  FROM t1_05 a
 WHERE a.f_name IN (SELECT b.f_name
                      FROM t1_05 b
                     WHERE b.f_name = a.f_name AND a.ROWID <> b.ROWID)

You can try with this query. But in create table scripts and insert table scripts, the table name different.

Regards,
Kiran.
Re: Duplicate Address [message #285656 is a reply to message #285649] Wed, 05 December 2007 04:17 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
SQL> select a.f_name||a.l_name,
  2  a.add_1||a.add_2||a.add_3||a.add_4,
  3  a.d_o_b,
  4  a.dr_lc_no,
  5  a.pass_no
  6  from t2 a
  7  where (a.f_name||a.l_name,
  8  a.add_1||a.add_2||a.add_3||a.add_4,
  9  a.d_o_b,
 10  a.dr_lc_no,
 11  a.pass_no)
 12  in ([B]select  a.f_name||a.l_name,
 13  a.add_1||a.add_2||a.add_3||a.add_4,
 14  a.d_o_b,
 15  a.dr_lc_no,
 16  a.pass_no
 17  from t2 a,t2 b
 18  where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
 19  ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
 20  a.d_o_b=b.d_o_b or
 21  a.dr_lc_no=b.dr_lc_no or
 22  a.pass_no=b.pass_no
 23  group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no
 24  having count(*)>1[/B] 25  )
 26  /

no rows selected


When i am executing the above query i am getting no rows selected, but when i split and excute i will return the below

SQL> select  a.f_name||a.l_name,
  2  a.add_1||a.add_2||a.add_3||a.add_4,
  3  a.d_o_b,
  4  a.dr_lc_no,
  5  a.pass_no
  6  from t2 a,t2 b
  7  where ltrim(rtrim(a.f_name||a.l_name))=ltrim(rtrim(b.f_name||b.l_name)) or
  8  ltrim(rtrim(a.add_1||a.add_2||a.add_3||a.add_4))=ltrim(rtrim(b.add_1||b.add_2||b.add_3||b.add_4)) or
  9  a.d_o_b=b.d_o_b or
 10  a.dr_lc_no=b.dr_lc_no or
 11  a.pass_no=b.pass_no
 12  group by a.f_name||a.l_name,a.add_1||a.add_2||a.add_3||a.add_4,a.d_o_b,a.dr_lc_no,a.pass_no
 13  having count(*)>1
 14  /

A.F_NAME||A.L_NAME                       A.ADD_1||A.ADD_2||A.ADD_3||A.ADD_4                                                     
---------------------------------------- ------------------------------------------------------------------------------------
FIRST NAMELAST NAME                      ADDRESS LINE 1
FIRST NAMELAST NAME                      ADDRESS LINE 1ADDRESS LINE 2
FIRST NAMELAST NAME                      ADDRESS LINE 1ADDRESS LINE 2
FIRST NAMELAST NAME                      ADDRESS LINE 1ADDRESS LINE 2ADDRESS LINE 3                                             
FIRST NAMELAST NAME                      ADDRESS LINE 1ADDRESS LINE 2ADDRESS LINE 3
FIRST NAMELAST NAME                      ADDRESS LINE 1ADDRESS LINE 2ADDRESS LINE 3ADDRESS LINE 4  
Re: Duplicate Address [message #285657 is a reply to message #284662] Wed, 05 December 2007 04:18 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

In create table script change the T1 as T2 


Ashok, you update your post then.

Kiran.
Re: Duplicate Address [message #285658 is a reply to message #285655] Wed, 05 December 2007 04:19 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I will change it as t2
Re: Duplicate Address [message #285660 is a reply to message #285657] Wed, 05 December 2007 04:20 Go to previous messageGo to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Sorry for typo mistakes
Previous Topic: Urgent SQL Assistance
Next Topic: Error in BLOB array
Goto Forum:
  


Current Time: Sun Dec 11 02:23:27 CST 2016

Total time taken to generate the page: 0.08587 seconds