Hi,
Looks like my other similar questions got closed, so asking a new question.
I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows:
create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue and not a #code issue.', 25940149);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue related to #database', 25768241);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#timing issue', 26167402);
I have following query
SELECT distinct bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL
which gives output like as follows:
BUG_NUMBER TAG
------------- ------
25940149 test
25940149 code
25768241 timing
25768241 database
26167402 timing
and there is another table
create table cust_bug_tag(BUG_NUMBER NUMBER REFERENCES cust_bug_data(BUG_NUMBER),tag VARCHAR(250), primary key (bug_nubmer, tag));
Now if I use following query it will insert 5 rows into cust_bug_tag
insert into cust_bug_tag(bug_number, tag)
SELECT distinct bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL
Now if I insert one more row(4th row) in my first table cust_bug_data
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#new issue, #test', 261672);
Then could you please let me know what would be the query which will only insert that 4th rows content into second table cust_bug_tag and cust_bug_tag should look like
BUG_NUMBER TAG
------------- ------
25940149 test
25940149 code
25768241 timing
25768241 database
26167402 timing
261672 new
261672 test
Actually I want to run following query in an interval(daily)
insert into cust_bug_tag(bug_number, tag)
SELECT distinct bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL
and this should insert only insert new(4th row here for example) rows entries(bug_number, tag) into cust_bug_tag.
cust_bug_data table will be updated daily and based on that only the delta rows should be inserted into cust_bug_tag.
There is one more issue. If the root_cause column in cust_bug_data doesn't have a #taged keyword then query should not insert any row in cust_bug_tag.
like insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a not an issue', 257241);
for above there should not be any (bug_number,tag) return row as there is not # in root_cause column and for this row there should not be any entry in cust_bug_tag
Thanks,
Subhash.
If you want to add bug_numbers that aren't already in the table, you just need a not exists clause:
create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue and not a #code issue.', 25940149);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue related to #database', 25768241);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#timing issue', 26167402);
create table cust_bug_tag(BUG_NUMBER NUMBER REFERENCES cust_bug_data(BUG_NUMBER),tag VARCHAR(250), primary key (bug_number, tag));
insert into cust_bug_tag(bug_number, tag)
SELECT distinct bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
select * from cust_bug_tag;
BUG_NUMBER TAG
26167402 timing
25940149 code
25768241 database
25940149 test
25768241 timing
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#new issue, #test', 261672);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a not an issue', 257241);
insert into cust_bug_tag(bug_number, tag)
SELECT distinct bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data d
where not exists (
select null from cust_bug_tag t
where t.bug_number = d.bug_number
)
and regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
select * from cust_bug_tag
order by 1 , 2;
BUG_NUMBER TAG
261672 new
261672 test
25768241 database
25768241 timing
25940149 code
25940149 test
26167402 timing
Of course, this means you're querying both tables instead of just inserting. Which increases the work you're doing. You're better off inserting the values into the tag table at the same time as you add them to data.
If you want to get fancy, you can do this in one statement with insert all!
To do this remove the regular expression from the where clause. Then only insert into tag when this is not null:
insert all
when rownum = 1 then into cust_bug_data (ROOT_CAUSE, BUG_NUMBER ) values (str, bug_number)
when tag is not null then into cust_bug_tag (bug_number, tag) values (bug_number, tag)
with rws as (
select 0 bug_number, '#test #stuff' str from dual
)
select bug_number, str, replace(regexp_substr(str,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
from rws
CONNECT BY regexp_substr(str,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
insert all
when rownum = 1 then into cust_bug_data (ROOT_CAUSE, BUG_NUMBER ) values (str, bug_number)
when tag is not null then into cust_bug_tag (bug_number, tag) values (bug_number, tag)
with rws as (
select 1 bug_number, 'test stuff' str from dual
)
select bug_number, str, replace(regexp_substr(str,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
from rws
CONNECT BY regexp_substr(str,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
select * from cust_bug_data;
ROOT_CAUSE BUG_NUMBER
This is a #test issue and not a #code issue. 25940149
This is a #timing issue related to #database 25768241
#timing issue 26167402
#new issue, #test 261672
This is a not an issue 257241
#test #stuff 0
test stuff 1
select * from cust_bug_tag
order by 1, 2;
BUG_NUMBER TAG
0 stuff
0 test
261672 new
261672 test
25768241 database
25768241 timing
25940149 code
25940149 test
26167402 timing