Query Analysis [message #666666] |
Mon, 20 November 2017 02:52 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hello,
We have a below requirement to compare two column values. The values in first table column should be compared with each comma separated values in second table column.
The matched values should be displayed correspondingly.
Create table Test_cc (cc_code varchar2(5) );
Create table Test_compare (url_list varchar2(100) );
insert into Test_compare values ('AA-HTTP://AA.COM, AC-HTTP://AC.COM,CC-HTTP://CC.COM, CA-HTTP://CA.COM');
Insert into Test_cc values ('AA');
Insert into Test_cc values ('AC');
Insert into Test_cc values ('CA');
commit;
The requirement output from query is,
AA AA-HTTP://AA.COM
AC AC-HTTP://AC.COM
CA CA-HTTP://CA.COM
Please advise.
Thanks,
SRK
|
|
|
|
|
Re: Query Analysis [message #666673 is a reply to message #666670] |
Mon, 20 November 2017 04:01 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col url format a30
SQL> with
2 url_list as (
3 select trim(regexp_substr(url_list, '([^,])+', 1, column_value)) url
4 from test_compare,
5 table(cast(multiset(select level from dual
6 connect by level <= regexp_count(url_list,',')+1)
7 as sys.odciNumberList))
8 )
9 select cc_code, replace(url,cc_code||'-HTTP', 'HTTP') url
10 from test_cc, url_list
11 where url like cc_code||'-HTTP%'
12 /
CC_CO URL
----- ------------------------------
AA HTTP://AA.COM
AC HTTP://AC.COM
CA HTTP://CA.COM
|
|
|
Re: Query Analysis [message #666675 is a reply to message #666673] |
Mon, 20 November 2017 04:29 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Since ORACLE 12 you can use LATERAL to avoid the TABLE CAST construct:
WITH test_compare (url_list) AS
(SELECT 'AA-HTTP://AA.COM, AC-HTTP://AC.COM,CC-HTTP://CC.COM, CA-HTTP://CA.COM' FROM dual),
test_cc(cc_code) AS
(SELECT 'AA' FROM dual UNION ALL
SELECT 'AC' FROM dual UNION ALL
SELECT 'CA' FROM dual),
url_list AS
(SELECT trim(regexp_substr(url_list, '([^,])+', 1, lv)) url
FROM test_compare,
LATERAL (SELECT level lv FROM dual CONNECT BY level <= regexp_count(url_list,',')+1))
SELECT cc_code, replace(url,cc_code||'-HTTP', 'HTTP') url
FROM test_cc, url_list
WHERE url LIKE cc_code||'-HTTP%';
CC_CO URL
----- ------------------------------
AA HTTP://AA.COM
AC HTTP://AC.COM
CA HTTP://CA.COM
|
|
|
|
|
|