Icy
Paul, October 08, 2006 - 3:40 pm UTC
Tom - that really is so cool ...
Paul
Clever SQL
PV Lakshminarasimhan, October 09, 2006 - 1:55 am UTC
It's really great. More than that, Oracle has such GR8 people like Mr.Tom who can explore the various means and ways by using analytics, to have optimum utilization of Oracle, especially to help the community using the same.
We are lucky to have such a site (including the rss feeds).
thanks once again.
problem running this query
A reader, October 09, 2006 - 2:45 am UTC
Tom when i execute the query i get the following error
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
please tell me how to proceed
October 09, 2006 - 8:28 am UTC
get to 9ir2?
problem running this query
A reader, October 09, 2006 - 2:46 am UTC
Tom when i execute the query i get the following error
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
please tell me how to proceed
For "A reader"
Muhammad Riaz Shahid, October 09, 2006 - 3:19 am UTC
You are using old version of sql*plus, use the newer version:
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 9 11:10:15 2006
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password: *****
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
scott@DB1> ed
Wrote file afiedt.buf
1 with aa as (select * from emp)
2* select * from aa
scott@DB1> /
SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 9 11:12:19 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password: *****
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
PL/SQL procedure successfully completed.
scott@CMAD> with aa as (select * from emp)
2 select * from aa;
<Result of above query here>
October 09, 2006 - 8:28 am UTC
thanks, sqlplus "pre-processes" queries - it peeks at the words you type before submitting to the server.
Here it could not figure out what with meant.
nordine, October 09, 2006 - 10:29 am UTC
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> select level 1 from dual connect by level <= 5;
LEVEL
----------
1
On your BLOG the same order produce this output:
SQL> select level l
2 from dual
3 connect by level <= 5;
L
----------
1
2
3
4
5
Is there any explanation to that?
Thanks a lot
Why "with data"?
A reader, October 09, 2006 - 12:55 pm UTC
I'm not sure I quite understand the use of "with data". The following seems to produce the same result set whether "with data" is used or not. Can you please explain the reason for using "with data"?
variable txt varchar2(25)
exec :txt := 'a,bb,ccc,d,e,f';
SELECT SUBSTR(txt,
INSTR(txt, ',', 1, level) + 1,
INSTR(txt, ',', 1, level + 1) - INSTR(txt, ',', 1, level) - 1) as token
FROM (SELECT ',' || :txt || ',' txt FROM dual)
CONNECT BY level <= LENGTH(:txt) - LENGTH(REPLACE(:txt, ',', '')) + 1;
TOKEN
----------------------------------
a
bb
ccc
d
e
f
October 09, 2006 - 1:47 pm UTC
because I am presuming you will be:
select *
from some_table
where some_column in (select * from data)
and select * from data is much easier on the eyes and brain to understand than having the actual query in there.
So, for the same reason you write small subroutines and not 5,000 line subroutines.
You solved a problem that has been bothering me for years
Vidya, October 30, 2006 - 7:49 pm UTC
Thank you so very much.
Now, I can use this to receive a comma separated list (of, say Customer IDs) from the FRONT-END application and return (the Customer details) in a CURSOR much more easily. Of course, I have to look out for performance issues, if any.
Till now, I was populating a VARRAY (requiring the declaration of two DB objects and one PLSQL variable) from the comma separated list (requiring about 30 lines of PLSQL code) and using it (by CASTing it) in the WHERE IN part of the SELECT statement.
but does this happen?
Scott Mattes, March 15, 2007 - 3:23 pm UTC
Tom,
Way WOW, but why does the output from your very first example at the top give
6 rows selected
txt
---------
a,bb,ccc,d,e,f
I ran this on 9.2.0.7.0
Thank you.
March 16, 2007 - 2:56 pm UTC
eh? cut and paste the entire example please, mine was run in 9ir2 as well. not sure what you mean.
ugh, should have previewed first
Scott Mattes, March 15, 2007 - 3:28 pm UTC
Tom,
The title of the previous post should have been "but why does this happen" and the output should have also showed the expected results
TOKEN
----------------------------------
a
bb
ccc
d
e
f
6 rows selected
txt
-------
a,bb,ccc,d,e,f
and I am asking why do the last two lines display?
Thank you
March 16, 2007 - 3:00 pm UTC
do you have autoprint on?
autoprint will print out the :bind_variables when they are referenced in SQL.
bascially you are printing the bind variable out
Wow
abdul wahab, March 17, 2007 - 7:21 am UTC
Hi Tom,
I also read this WOW query in Oracle Magazine march/april 2007 in your column. I enjoyed stidying it's logic.
But going back to the initial question of this thread.. Can you explain us how you formulated this query..i mean how do you create the logic/magic with variables. all that subtracting and dividing stuff.
March 17, 2007 - 5:20 pm UTC
just "logic", the trick was getting N rows numbered 1..N
Once you have that, just a bunch of substrings...
Dominic Brooks, March 18, 2007 - 4:01 am UTC
Great stuff as usual.
I have recently had cause to recommend a similar solution at a client with exactly the problems you would expect from n SQL statements with 1 ... m length IN lists.
It's a shame that OCI has a 4000 limit on VARCHAR2 bound binds as this limits such an approach (although it may be that subsequent attempts to use further workarounds USING LONG/CLOB have been erroneously implemented).
As ever, I think this may often be a workaround for application developers / database designers not using appropriate application->database interfaces (stored procs, O-R views, Oracle collections).
This raises a current frustration, for me, which is that ODP does not support Oracle collections to the same extent as JDBC (which is probably why the application initially resorted to using varying IN lists).
Good One..
Kiran Kumar, March 19, 2007 - 4:21 am UTC
I really appriciate Tom, you are doing such a great job by answering various questions and doubts. this site is really useful. I am a regular reader of this site and enjoying a lot. Keep continue..
Clever Ruby
Casimir Saternos, March 19, 2007 - 12:26 pm UTC
Definitely an interesting solution - and demonstrates the flexibility of Oracle SQL. However, I think that the complexity of the solution suggests that is a problem better solved in another language - for instance, in Ruby:
irb(main):004:0> 'a,bb,ccc,d,e,f'.split(',').each{|t|puts t}
a
bb
ccc
d
e
f
=> ["a", "bb", "ccc", "d", "e", "f"]
Thank you for the creative solution and your tireless efforts - I have benefited greatly from your books and online resources.
WOW
A reader, April 10, 2007 - 4:27 am UTC
Hi Tom,
I tried the wow methoed for lob parsing .
meaning , I have a lob with lines seperated by semicolons.
however, I get an error
Error at line 5
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
with data
as
(
select substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
-- from (select ','||:txt||',' txt from dual)
from (select blob_content txt
from wwv_flow_file_objects$
where filename='test.sql')
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select * from data;
is there a way to do that ?
10x,
sagsag
my mistake
A reader, April 10, 2007 - 4:52 am UTC
working on BLOB is not a good idea
I added the utl_raw.cast_to_varchar2 and it works fine.
still have some problems .to workout.
regards,
sagsag
With a longer delimiter
dave, July 11, 2007 - 3:21 pm UTC
Hi Tom,
Thank you very much for the site. I have found it, and your book very useful indeed.
I had an opportunity to use the query at the top of this thread today, but with a delimiter of more than one character. I thought I would share the modified version. Not rocket science, but might save someone some time.
SQL> variable txt varchar2(25)
SQL> variable dl varchar2(3)
SQL>
SQL> exec :txt := 'a!@!bb!@!ccc!@!d!@!e!@!f'; :dl := '!@!';
PL/SQL procedure successfully completed.
SQL>
SQL> with data
2 as
3 (
4 select substr (txt,
5 instr (txt, :dl, 1, level ) + dlen,
6 instr (txt, :dl, 1, level+1) - instr (txt, :dl, 1, level) - dlen )
7 as token
8 from (select :dl||:txt||:dl txt, length(:dl) dlen from dual)
9 connect by level <= (length(:txt)-length(replace(:txt,:dl,'')))/dlen+1
10 )
11 select * from data;
TOKEN
--------
a
bb
ccc
d
e
f
with data as select
James, November 19, 2007 - 11:00 am UTC
Only one problem...
I have no idea what "with data as select" actually does.
I've searched on google for a while and not found any explanation, syntax or discussion. Just a few 'tricks.'
Is "with data" as old as the hills? Is it like trying to find out what 'select' does? I only ask as I've never coded a "with data" query and would like to read about it: when to use it, how it performs, syntax, capabilities.
I feel so inadequate.
CMG, November 21, 2007 - 9:01 am UTC
the word "data" here is just the alias assigned to the query that's defined by the "select" statement. You could just as easily use "Tom" instead of "data".
WITH tom AS SELECT 'x' FROM DUAL ...
just means I'm going to define a query (SELECT 'x' FROM DUAL) which I'll refer to from this point onwards as if it were a table called "tom". Then all references to "tom" actually refer to the results of that query.
If you look at the explain plan for queries using WITH clauses, you can see that the query is executed early on and stored as a virtual table which is then used later on.
subquery factoring - reuse of results
Richard Smith, November 21, 2007 - 11:21 am UTC
CMG: Actually, it depends on your usage of the results from the subquery factoring. If you use it to move some inline view out of the main query (for readability) and that data is referenced only once, the optimizer will probably treat it as if it were still an inline view, using the usual complex view merging techniques.
If, however, your factored subquery is used multiple times in the main query, it very likely will be stored as a virtual table and the results reused by the main query, which should result in faster response overall. It all depends on the cost of pre-computing the subquery and joining it to the main query results.
Not binding but a row from a different table
rc, November 26, 2007 - 3:39 pm UTC
(oracle 11.1.0.6.0)
You don't always want to bind an inlist, sometimes you want to select the inlist from a table. I came with this solution:
SQL> create table testclob (id number(10) , list clob) ;
Tabel is aangemaakt.
SQL> insert into testclob values (1,'a1,a2,a3');
1 rij is aangemaakt.
SQL> select substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
from
(
select ','||list||',' txt , list from testclob
) t
connect by level <= length(t.list)-length(replace(t.list,',',''))+1
;
TOKEN
-------
a1
a2
a3
/* for geeks only:
What however if you insert another row into testclob?
insert into testclob values (2,'b1,b2,b3');
SQL> select substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
from
(
select ','||list||',' txt , list from testclob
) t
connect by level <= length(t.list)-length(replace(t.list,',',''))+1
;
TOKEN
-------------------------------
a1
a2
a3
b3
b2
a3
b3
b1
a2
a3
b3
b2
a3
b3
14 rijen zijn geselecteerd.
14 = 4 * 2 + 2 * 2 + 1 * 2
delete testclob;
insert into testclob values (1,'a1,a2,a3,a4');
insert into testclob values (2,'b1,b2,b3,b4');
will give 30 rows, 30 = 8 * 2 + 4 * 2 + 2 * 2 + 1 * 2
*/
November 27, 2007 - 3:03 pm UTC
well, if you have a table, one would question "why do not you just STORE ROWS"
I would reject this code in real life, there is absolutely no logical reason to store:
insert into testclob values (1,'a1,a2,a3,a4');
insert into testclob values (2,'b1,b2,b3,b4');
you should have stored:
(1,'a1')
(1,'a2')
(1,'a3')
....
and just use a subquery, no tricks.
settings and trees
rc, November 28, 2007 - 4:19 pm UTC
Sometimes you have settings table where customers can do all kind of different settings:
create table settings (name varchar2(25), setting varchar2(200) );
Customers can use this to configure our applications.
We sometimes have settings like:
name setting
--------------- ----------
LAW_OWNER_CODES BEL,JUI,FAL
We want to built queries like:
select * from real_estate
where lawcode in ('BEL','JUI','FAL');
But I understand that you want me to create an extra table "settings_children" with a foreign key relation to "settings".
The geeks only part is more about trees than the "binding in list":
I know understand that you can you use 'connect by level < ' to generate binary trees (and all the paths in a binary tree) if you have a table with two rows instead of one:
SQL> create table roots ( root number(1) );
Tabel is aangemaakt.
SQL> insert into roots values (1);
1 rij is aangemaakt.
SQL> insert into roots values (2) ;
1 rij is aangemaakt.
select path from
(
select root, substr(sys_connect_by_path(root,'/'),1,30) path
from
(select root from roots)
connect by level < 4
)
where path like '/1%';
gives:
PATH
------------------------------
/1
/1/1
/1/1/1
/1/1/2
/1/2
/1/2/1
/1/2/2
7 rijen zijn geselecteerd.
Tree:
1
/ \
1 2
/ \ / \
1 2 1 2
If you
insert into roots values (3);
you can generate a trinary tree.
PATH
------------------------------
/1
/1/1
/1/1/1
/1/1/2
/1/1/3
/1/2
/1/2/1
/1/2/2
/1/2/3
/1/3
/1/3/1
/1/3/2
/1/3/3
13 rijen zijn geselecteerd.
I don't know if generating binary or trinary trees is useful but I believe it is fun!
generic tree view generator
rc, November 28, 2007 - 4:46 pm UTC
variable l_level number
variable l_branches number
exec :l_level := 4;
exec :l_branches := 3;
select path from
(
select num, substr(sys_connect_by_path(num,'/'),1,30) path
from
( select level num from dual connect by level < :l_branches+1)
connect by level < :l_level+1
)
where path like '/1%';
SQL Query Problem
A Reader, December 07, 2007 - 11:43 am UTC
I have a table RELS like this :
RELS
===========================================
E1 E2 Relation
===========================================
Dharm Sunny Deol Father
Sunny Deol Bobby Deol Brother
Hema Dharm Wife
Esha Hema Daughter
===========================================
Please help me to find the name of people who are orphans.
The answer should be :
Dharm
Hema
Thanks
Is this doable in SQL
J B, December 21, 2007 - 2:44 am UTC
Hi tom,
I tried to write SQL for this model, but couldn't achieve the satisfactory SQL so far.
Just want to ask if this is doable?
Thanks
December 21, 2007 - 2:53 pm UTC
I have no idea what you mean.
Writing SQL
Jb, December 21, 2007 - 11:51 pm UTC
Hi Tom,
The above mentioned question, where the reader is asking to find the name of persons who are "orphans", is that doable in SQL.
I am not sure if that can be done. Just wanted to have your comments as you are the best in the SQL writing business.
I hope it clarifies.
December 22, 2007 - 11:50 am UTC
hah, that orphan example was so ill specified - I won't even read it.
RELS
===========================================
E1 E2 Relation
===========================================
Dharm Sunny Deol Father
so, is Dharm the father of Sunny Deol or vice verse.
no create table
no inserts
NO LOOK either.
The Orphans
rc, December 22, 2007 - 12:40 pm UTC
I hope this is the solution for the orphan question. It is a complicated problem (imho) so this solution needs a lot of testing!!
I believe Dharm is the father of Sunny Deol.
drop table rels;
create table rels (e1 varchar2(20) , e2 varchar2(20) , relation varchar2(20) );
insert into rels values ('Dharm','Sunny','Father');
insert into rels values ('Sunny','Bobby','Brother');
insert into rels values ('Hema','Dharm','Wife');
insert into rels values ('Esha','Hema','Daughter');
insert into rels values ('Sunny','John','Brother');
insert into rels values ('Dharm','Iris','Brother');
insert into rels values ('Marc','Iris','Brother');
insert into rels values ('Bo','Dharm','Mother');
insert into rels values ('Ha','Ra', 'Broher');
commit;
create or replace view not_orphans
as
select distinct e1,e2,relation
from
(
with parents as
(
select distinct e1 ep
from relsv
where relation = 'Parent'
)
select level l,e1,e2,relation
from (
select e1,e2,'Parent' relation
from rels
where relation in ('Father','Mother')
union
select e2,e1,'Parent'
from rels
where relation in ('Son','Daughter')
union
select e1,e2,'Sibling'
from rels
where relation in ('Sister','Brother')
union
select e2,e1,'Sibling'
from rels
where relation in ('Sister','Brother')
)
, parents
connect by nocycle e1 = prior e2
start with e1=ep
)
where l > 1
/
select e1
from rels
where e1 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
and relation not in ('Daughter','Son')
union
select e2
from rels
where e2 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
/
orphan question, oops mistake
rc, December 22, 2007 - 12:55 pm UTC
drop table rels;
create table rels (e1 varchar2(20) , e2 varchar2(20) , relation varchar2(20) );
insert into rels values ('Dharm','Sunny','Father');
insert into rels values ('Sunny','Bobby','Brother');
insert into rels values ('Hema','Dharm','Wife');
insert into rels values ('Esha','Hema','Daughter');
insert into rels values ('Sunny','John','Brother');
insert into rels values ('Dharm','Iris','Brother');
insert into rels values ('Marc','Iris','Brother');
insert into rels values ('Bo','Dharm','Mother');
insert into rels values ('Ha','Ra', 'Broher');
commit;
First simplify the relations and double the sibling relations:
create or replace view relsv
as
select e1,e2,'Parent' relation
from rels
where relation in ('Father','Mother')
union
select e2,e1,'Parent'
from rels
where relation in ('Son','Daughter')
union
select e1,e2,'Sibling'
from rels
where relation in ('Sister','Brother')
union
select e2,e1,'Sibling'
from rels
where relation in ('Sister','Brother')
/
create or replace view not_orphans
as
select distinct e1,e2,relation
from
(
with parents as
(
select distinct e1 ep
from relsv
where relation = 'Parent'
)
select level l,e1,e2,relation
from relsv
, parents
connect by nocycle e1 = prior e2
start with e1=ep
)
where l > 1
/
I use nocycle because we have doubled the sibling relations.
select e1 orphan
from rels
where e1 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
and relation not in ('Daughter','Son')
union
select e2
from rels
where e2 not in
(
select e1 from not_orphans
union
select e2 from not_orphans
)
/
ORPHAN
--------------------
Bo
Ha
Hema
Ra
I hope this works!!
orphan question
rc, December 23, 2007 - 4:24 am UTC
It doesn't work, I give up.
{One of the mistakes is that I didn't create a check constraint on the relation column}.
Some Help
JB, December 26, 2007 - 6:47 am UTC
Hi Tom,
Very complex problem.
Dharm being the father of Sunny, how can we derive the SQL to find the orphans.
I am putting the CREATE table and Inserts for you.
CREATE TABLE RELS
( E1 VARCHAR2(20),
E2 VARCHAR2(20),
REL VARCHAR2(20)
)
/
Insert into Rels Values ('Esha','Hema','Daughter');
Insert into Rels Values ('Dharm','Sunny','Father');
Insert into Rels Values ('Bobby','Sunny','Brother');
Insert into Rels Values ('Hema','Dharm','Wife');
Your help is appreciated.
Many thanks in advance.
December 26, 2007 - 9:26 am UTC
now, define orphan.
This way of storing relations is sort of, well, not sort - entirely wrong.
You have "X" is father of "Y".
And "A" is daughter of "B" which implies that "B" is either the father or mother of "A", but you don't state that.
No, I'm not going to work on this - too many loose ends here, from an ill defined problem to data that just isn't stored in a logical fashion.
Esha is not an orphan (Hema is mother or Father, I guess since someone named Hema is the wife of Dharm - Hema is the mother)
Sunny is not an orphan, since Dharm is his father
And by extension, assuming Bobby is the full brother of Sunny - Dharm is also his father. So Bobby is not.
But given the information here, I'd have to say that Hema and Dharm may or may not be orphans themselves.
No, this isn't a database problem... The database problem would be how to store this data in the first place - and we are not there yet.
failed to find any documentation
A Reader, December 26, 2007 - 10:25 am UTC
Hi Tom
in wow query
with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )
i have failed to find any documentation for the connect by level clause. can to point us to the documentation regarding connect by level clause.
Orhan question
RC, December 27, 2007 - 6:27 am UTC
Dear JB, why don't you want to learn from my mistakes?
Please create a check constraint on the relation column!
What kind of relations are we talking about? (Father, Mother, Husband, Wife, Son, Daughter, Brother, Sister?) A check constraint will help and protect you from making errors.
Fist we have to define the question properly.
To: rc from The Netherlands, posted on: November 26, 2007 - 3pm US/Eastern.
Kashif, March 14, 2008 - 11:08 am UTC
RC,
To handle multiple rows that contain delimited data (e.g. as in a table) instead of a single row that is bound in, you have to treat the substr/instr (Tom's) query as a collection subquery, and then unnest the collection using a table expression (TABLE()). I simply added a VARCHAR2 table SQL type to your original requirements:
<code>
$ @a
Connected.
a@CASST
$ create table testclob (id number(10) , list clob) ;
Table created.
a@CASST
$ insert into testclob values (1,'a1,a2,a3');
1 row created.
a@CASST
$ select substr (txt,
2 instr (txt, ',', 1, level ) + 1,
3 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
4 from
5 (
6 select ','||list||',' txt , list from testclob
7 ) t
8 connect by level <= length(t.list)-length(replace(t.list,',',''))+1
9 ;
TOKEN
--------------------------------------------------------------------------------
a1
a2
a3
3 rows selected.
a@CASST
$ insert into testclob values (2,'b1,b2,b3');
1 row created.
a@CASST
$ CREATE OR REPLACE
2 TYPE t_vc2_50 as table of varchar2(50)
3 /
Type created.
a@CASST
$ select b.column_value token
2 from (select id, list, ','||list||',' txt from testclob ) t,
3 table ( cast ( multiset (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) token
7 from dual
8 connect by level <= length(t.list)-length(replace(t.list,',',''))+1
9 ) as t_vc2_50 ) ) b
10 /
TOKEN
--------------------------------------------------
a1
a2
a3
b1
b2
b3
6 rows selected.
Kashif
</code>
reverse the operation
grimms, April 08, 2009 - 7:48 am UTC
Given the table:
TOKEN
----------------------------------
a
bb
ccc
d
e
f
6 rows selected.
Is there also a SQL statement solution that takes it back to comma sep form ?
i.e. output from SQL would be
'a,bb,ccc,d,e,f'
April 13, 2009 - 1:27 pm UTC
ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 5;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select substr(max(sys_connect_by_path(username,',')),2) data
2 from (select username, rownum rn from t)
3 start with rn = 1
4 connect by prior rn+1 = rn
5 /
DATA
--------------------------------------------------
BIG_TABLE,DIP,TSMSYS,LOTTOUSER,MDDATA
use row_number() over (order by whatever) rn
instead of rownum if you want the string in any sort of predicable 'order'
A reader, January 28, 2010 - 9:55 am UTC
Is there anyway I can use a similar technique that you showed in the beginning of this thread (turning a comma-separated string into a table) to do the following?
Table t stores a date range. In this example the range is 2010-01-23 ~ 2010-01-28. There can only be one record in this table.
Each record in table x has a date range that is within the date range stored in table t. I'd like to do an equivalent of MINUS set operation
<Date range specified in table t>
MINUS
<Date range in individual records in table x>;
and displays the differences in rows.
CREATE TABLE t (
StartDate DATE,
EndDate DATE,
);
INSERT INTO t VALUES (TO_DATE('2010-01-23', 'yyyy-mm-dd'), TO_DATE('2010-01-28', 'yyyy-mm-dd'));
CREATE TABLE x (
id INTEGER,
StartDate DATE,
EndDate DATE
);
INSERT INTO x VALUES (1, TO_DATE('2010-01-24', 'yyyy-mm-dd'), TO_DATE('2010-01-27', 'yyyy-mm-dd'));
INSERT INTO x VALUES (2, TO_DATE('2010-01-25', 'yyyy-mm-dd'), TO_DATE('2010-01-26', 'yyyy-mm-dd'));
-- ---------------------------------------------------------------------------
-- Desired output
-- ---------------------------------------------------------------------------
1, 2010-01-23
1, 2010-01-28
2, 2010-01-23
2, 2010-01-24
2, 2010-01-27
2, 2010-01-28
January 29, 2010 - 3:52 pm UTC
ops$tkyte%ORA11GR2> with data1
2 as
3 (select startdate+level-1 dt
4 from t
5 connect by level <= enddate-startdate+1
6 ),
7 data2
8 as
9 (select x.id, x.startdate, x.enddate, y.column_value dt
10 from x, table( cast( multiset( select startdate+level-1
11 from dual
12 connect by level <= x.enddate-x.startdate+1) as sys.odcidatelist) ) y
13 )
14 select id, data1.dt
15 from data2 partition by (id) right outer join data1 on (data1.dt = data2.dt)
16 where data2.dt is null
17 order by id, data1.dt
18 /
ID DT
---------- ---------
1 23-JAN-10
1 28-JAN-10
2 23-JAN-10
2 24-JAN-10
2 27-JAN-10
2 28-JAN-10
6 rows selected.
link no found
A reader, January 30, 2010 - 6:32 pm UTC
To a reader
MH, February 01, 2010 - 9:08 am UTC
Sql Problem ; Joke
A reader, February 01, 2010 - 12:18 pm UTC
The answer should be :
Dharm
Hema
What about Basanti and Veeru and Gabbar Singh ?
Clever SQL - 10g
Rajeshwaran, Jeyabal, February 03, 2010 - 10:52 pm UTC
Tom,
Here is the " Clever SQL " Version in 10g.
scott@10G> variable txt varchar2(25);
scott@10G> exec :txt := 'a,bb,ccc,d,e,f';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
scott@10G> select trim(regexp_substr(upper(:txt),'[^,]+',1,level)) as Tokens
2 from dual
3 connect by level <= length(regexp_replace(:txt,'[^,]'))+1;
TOKENS
--------------------------------
A
BB
CCC
D
E
F
6 rows selected.
Elapsed: 00:00:00.00
February 04, 2010 - 12:22 pm UTC
I'll stick with length and substr - regexp, neat but a HUGE cpu cost.
Clever SQL - 10g
Rajeshwaran, Jeyabal, February 09, 2010 - 2:41 am UTC
Tom,
You are right..REGEX are HUGE cpu cost.
scott@10G> create table T
2 (
3 x number,
4 y varchar2(4000)
5 );
Table created.
Elapsed: 00:00:00.31
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE T
(
x RECNUM,
y POSITION(1:4000)
)
BEGINDATA
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
.........
|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|
scott@10G> DECLARE
2 l_tokens dbms_sql.varchar2a;
3 begin
4 for r in (select y from t)
5 loop
6 select substr (txt,
7 instr (txt, '|', 1, level ) + 1,
8 instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
9 as token
10 bulk collect into l_tokens
11 from (select r.y as txt FROM dual)
12 connect by level <= length(txt) - length(replace(txt,'|'))-1;
13 l_tokens.DELETE;
14 end loop;
15
16 for r in (select y from t)
17 loop
18 select trim(regexp_substr((txt),'[^|]+',1,level)) as Tokens
19 bulk collect into l_tokens
20 from (select r.y as txt FROM dual)
21 connect by level <= length(regexp_replace(txt,'[^|]'))-1;
22 l_tokens.DELETE;
23 end loop;
24 end;
25 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:30.23
********************************************************************************
DECLARE
l_tokens dbms_sql.varchar2a;
begin
for r in (select y from t)
loop
select substr (txt,
instr (txt, '|', 1, level ) + 1,
instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
as token
bulk collect into l_tokens
from (select r.y as txt FROM dual)
connect by level <= length(txt) - length(replace(txt,'|'))-1;
l_tokens.DELETE;
end loop;
for r in (select y from t)
loop
select trim(regexp_substr((txt),'[^|]+',1,level)) as Tokens
bulk collect into l_tokens
from (select r.y as txt FROM dual)
connect by level <= length(regexp_replace(txt,'[^|]'))-1;
l_tokens.DELETE;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 2.00 2.38 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.01 2.39 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SELECT SUBSTR (TXT, INSTR (TXT, '|', 1, LEVEL ) + 1, INSTR (TXT, '|', 1,
LEVEL+1) - INSTR (TXT, '|', 1, LEVEL) -1 ) AS TOKEN
FROM
(SELECT :B1 AS TXT FROM DUAL) CONNECT BY LEVEL <= LENGTH(TXT) -
LENGTH(REPLACE(TXT,'|'))-1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 15400 1.43 1.44 0 0 0 0
Fetch 15400 2.75 2.72 0 0 0 400400
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30801 4.20 4.17 0 0 0 400400
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
400400 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=2696889 us)
15400 FAST DUAL (cr=0 pr=0 pw=0 time=46042 us)
********************************************************************************
SELECT TRIM(REGEXP_SUBSTR((TXT),'[^|]+',1,LEVEL)) AS TOKENS
FROM
(SELECT :B1 AS TXT FROM DUAL) CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(TXT,
'[^|]'))-1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 15400 1.54 1.48 0 0 0 0
Fetch 15400 21.96 21.98 0 0 0 400400
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30801 23.51 23.46 0 0 0 400400
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
400400 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=14184509 us)
15400 FAST DUAL (cr=0 pr=0 pw=0 time=46371 us)
********************************************************************************
query detail needed
friend, August 26, 2011 - 3:34 am UTC
hi ,
i have a table having three columns name ,phone_no,offic_no
and having data like ..
name phone_no office_no
abc 1234,4568,1485 1236/5896/5214
bcv 1475/5289/4156 1458/6985,4568
and i need a query which should display result like this
name phone_no office_no
abc 1234
abc 4568
abc 1485
abc 1236
abc 5896
abc 5214
August 30, 2011 - 4:34 pm UTC
no create
no inserts
no look
I hate you data model by the way, if I can call it that. What motivates people to store data like that???? I'll never understand.
query detail needed
Rajeshwaran, Jeyabal, August 30, 2011 - 7:31 pm UTC
rajesh@ORA10GR2> create or replace type num_type is table of number;
2 /
Type created.
Elapsed: 00:00:00.09
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace function f(x in varchar2)
2 return num_type
3 pipelined is
4 l_data varchar2(30) := replace(x,',','/');
5 begin
6
7 for r in (select substr( txt,
8 instr(txt,'/',1,level) + 1,
9 instr(txt,'/',1,level+1) - instr(txt,'/',1,level) - 1) as y
10 from ( select '/'||l_data||'/' as txt from dual)
11 connect by level <= length(l_data) - length(replace(l_data,'/')) + 1 )
12 loop
13 pipe row ( to_number(r.y) );
14 dbms_output.put_line (r.y);
15 end loop;
16 end;
17 /
Function created.
Elapsed: 00:00:00.10
rajesh@ORA10GR2> create table t(x varchar2(30),y varchar2(30),z varchar2(30));
Table created.
Elapsed: 00:00:00.20
rajesh@ORA10GR2> insert into t values ('abc','1234,4568,1485','1236/5896/5214');
1 row created.
Elapsed: 00:00:00.17
rajesh@ORA10GR2> insert into t values ('bcv','1475/5289/4156','1458/6985,4568');
1 row created.
Elapsed: 00:00:00.09
rajesh@ORA10GR2> commit;
Commit complete.
Elapsed: 00:00:00.09
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select t1.x,t2.column_value
2 from (
3 select x,decode(l,1,y,2,z) as val
4 from t,(select level l from dual connect by level < = 2)
5 )t1, table(f(t1.val)) t2
6 /
X COLUMN_VALUE
---------- ------------
abc 1234
abc 4568
abc 1485
bcv 1475
bcv 5289
bcv 4156
abc 1236
abc 5896
abc 5214
bcv 1458
bcv 6985
bcv 4568
12 rows selected.
rajesh@ORA10GR2>
rajesh@ORA10GR2>
query detail needed
Rajeshwaran, Jeyabal, August 30, 2011 - 8:57 pm UTC
As per your question, here is the query to display the results in two different columns, phone_no# & office_no#
(That I missed out in first reply)
rajesh@ORA10GR2> select t1.x,
2 max(case when l = 1 then t2.column_value end) as phone_no,
3 max(case when l = 2 then t2.column_value end) as office_no
4 from (
5 select x,decode(l,1,y,2,z) as val,l
6 from t,(select level l from dual connect by level < = 2)
7 )t1, table(f(t1.val)) t2
8 group by t1.x,
9 case when l = 1 then t2.column_value end,
10 case when l = 2 then t2.column_value end
11 order by 1;
X PHONE_NO OFFICE_NO
----- --------- ---------
abc 1234
abc 1485
abc 4568
abc 1236
abc 5214
abc 5896
bcv 1475
bcv 4156
bcv 5289
bcv 1458
bcv 4568
bcv 6985
12 rows selected.
Elapsed: 00:00:00.06
rajesh@ORA10GR2>
The date range solution
Jichao Li, August 31, 2011 - 1:44 am UTC
Regexp learning
Nagaraju, July 10, 2013 - 11:20 am UTC
Hi Tom,
I didn't know that, Regexp are cpu intensive. thanks for this. I find it little difficult to learn as well. However I find this odd behavior with regexp, because I understand [0-9] and [[:digit:]] are same. But in the below case, [0-9] works, [[:digit:]] don't.
SQL> select regexp_substr('(906) 553-8725','\([[:digit]]{3}\)') phone_number from dual;
P
-
SQL> select regexp_substr('(906) 553-8725','\([0-9]{3}\)') phone_number from dual;
PHONE
-----
(906)
not sure, why?
July 16, 2013 - 3:34 pm UTC
ops$tkyte%ORA11GR2> select regexp_substr('(906) 553-8725','\([[:digit:]]{3}\)') phone_number
2 from dual;
PHONE
-----
(906)
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select regexp_substr('(906) 553-8725','\([0-9]{3}\)') phone_number from
2 dual;
PHONE
-----
(906)
missing a colon
Awesome... so how exactly does it work?
Miri Cc, December 22, 2015 - 4:21 pm UTC
I have used the level/ connect by level idea that I copied from another co-worker, but I really want to understand how it works. Can you explain in detail? I'm a novice.
December 23, 2015 - 12:29 am UTC
Sorry, are you talking level/connect by in general, or this specific string parsing example ?
SQL - Wishing happy holidays and merry christmas
Rajeshwaran, Jeyabal, December 25, 2015 - 9:34 am UTC
rajesh@ORA11G> select decode
2 (sign (floor (maxwidth/2) - rownum)
3 ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
4 ||rpad ('*', 2 * (rownum -1), ' *')
5 ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
6 from dual
7 , (select 40 as maxwidth from dual)
8 connect by level < floor (maxwidth/2) + 5
9 /
Merry Christmas
---------------------------------------------------------------------------
*
* *
* * *
* * * *
* * * * *
* * * * * *
* * * * * * *
* * * * * * * *
* * * * * * * * *
* * * * * * * * * *
* * * * * * * * * * *
* * * * * * * * * * * *
* * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * *
* * *
* * *
* * *
* * *
* * *
24 rows selected.