Home » SQL & PL/SQL » SQL & PL/SQL » Query Analysis (Oracle 12c)
Query Analysis [message #666666] Mon, 20 November 2017 02:52 Go to next message
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 #666668 is a reply to message #666666] Mon, 20 November 2017 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Define "matched".
Quote:
AA AA-HTTP://AA.COM
Is this because there is AA-HTTP or because of AA.COM of because there is AA anywhere?
Re: Query Analysis [message #666670 is a reply to message #666668] Mon, 20 November 2017 03:40 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks Michel for your response!!

The exact requirement is below.

"AA" should match with "AA-HTTP".
Similarly, "AC" with "AC-HTTP"
and "CA" with "CA-HTTP".

After matching, it should show results for url_list column starting with "HTTP".
Output below:

AA HTTP://AA.COM
AC HTTP://AC.COM
CA HTTP://CA.COM

Thanks,
SRK


Re: Query Analysis [message #666673 is a reply to message #666670] Mon, 20 November 2017 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 Go to previous messageGo to next message
_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

Re: Query Analysis [message #666677 is a reply to message #666673] Mon, 20 November 2017 04:35 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks Michel for your complex query!!

It does not looks simple what I thought!!

uff Thanks again:)
Re: Query Analysis [message #666678 is a reply to message #666675] Mon, 20 November 2017 04:36 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks you Razz
Re: Query Analysis [message #666681 is a reply to message #666675] Mon, 20 November 2017 04:53 Go to previous message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks Jum
Previous Topic: Passing warning collection from PL/SQL
Next Topic: grouping in matrix
Goto Forum:
  


Current Time: Thu Mar 28 09:16:10 CDT 2024