| Bulk Longest Match Query [message #187404] |
Sun, 13 August 2006 11:07  |
weekend79
Messages: 198
Registered: April 2005
Location: Islamabad
|
Senior Member |
|
|
Hi
TableA
Prefix varchar2(10),
Descriptions varchar2 (50),
TableB
Numbers varchar2(30),
Area varchar2(100)
I want a query that match TableA.Prefix with TableB.Numbers in longest match fashion starting from left.
i.e.
Prefix
22
224
22559
Numbers
224498756
225597784
225515587
122559711
I need a query which match above as follows
224498756 with 224
225597784 with 22559
225515587 with 22 (not with 22559 as it not match fully with number)
122559711 with Null
I have to compare massive data so I will be more thankful if the query will be efficient.
Following is the script to create above table and load same data.
CREATE TABLE tableA
(
Prefix varchar2(10),
Descriptions varchar2 (50)
);
CREATE TABLE TableB
(
Numbers varchar2(30),
Area varchar2(100)
);
INSERT INTO tableA VALUES ('22','test');
INSERT INTO tableA VALUES ('224','test');
INSERT INTO tableA VALUES ('22559','test');
INSERT INTO tableB VALUES ('224498756','test');
INSERT INTO tableB VALUES ('225597784','test');
INSERT INTO tableB VALUES ('225515587','test');
INSERT INTO tableB VALUES ('122559711','test');
Wishes
Jawad
[Updated on: Sun, 13 August 2006 11:08] Report message to a moderator |
|
|
|
| Re: Bulk Longest Match Query [message #187422 is a reply to message #187404] |
Sun, 13 August 2006 22:43  |
hobbes
Messages: 173
Registered: January 2006
|
Senior Member |
|
|
Does this help?
select b.numbers, d.prefix
from tableB b,
(
select b.numbers, max(a.prefix) prefix
from tableA a,
tableB b,
(select rownum r
from (select 1 from dual
connect by level <= 30)
) c
where c.r <= length(b.numbers)
and a.prefix = substr(b.numbers, 1, r)
group by b.numbers
) d
where b.numbers = d.numbers (+)
|
|
|
|
| Re: Bulk Longest Match Query [message #187467 is a reply to message #187422] |
Mon, 14 August 2006 02:34 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Analytic solution (aren't they gorgeous! I just wish I'd ever run into a real world problem where I could use them)
1 select distinct b.numbers
2 ,first_value(a.prefix) over (partition by b.numbers order by length(a.prefix) desc) prefix
3 from tablea a, tableb b
4* where b.numbers like a.prefix||'%'
SQL> /
NUMBERS |PREFIX
------------------------------|----------
224498756 |224
225515587 |22
225597784 |22559 |
|
|
|
|
|
|
|
|
|
|
|
| Re: Bulk Longest Match Query [message #187565 is a reply to message #187534] |
Mon, 14 August 2006 08:39 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, I was looking to get rid of the distinct for reasons of elegance, and to remove an extra sort step in the plan, but if it is a requirement, this is the best I've got so far:
select numbers,prefix
from (select b.numbers
,first_value(a.prefix) over (partition by b.numbers order by length(a.prefix) desc) prefix
,row_number() over (partition by b.numbers order by length(a.prefix) desc) rnum
from tablea a, tableb b
where b.numbers like a.prefix||'%')
where rnum = 1; |
|
|
|
|
|
| Re: Bulk Longest Match Query [message #187689 is a reply to message #187515] |
Mon, 14 August 2006 21:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| JRowbottom wrote on Mon, 14 August 2006 20:38 |
I wish I could work out a way of doing it without the DISTINCT though.
|
1 select b.numbers
2 ,min(a.prefix) keep (dense_rank last order by length(a.prefix))
3 from tablea a, tableb b
4 where b.numbers like a.prefix||'%'
5* group by b.numbers
NUMBERS MIN(A.PREF
------------------------------ ----------
224498756 224
225515587 22
225597784 22559
Let's not forget the ugly step-sister of the Analytic Function: the KEEP clause for aggregate functions.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
[and I empathize with your feelings about DISTINCT!]