Saturday, June 18, 2005

Connect By

I couldn't resist sharing this one, which I found on DBA-Support recently:

http://www.dbasupport.com/forums/showthread.php?t=47760

The question was how to create a column as the concatentation of many rows into one, without using PL/SQL: just a single query.

Here is the example given:

CREATE TABLE t1 (col1 VARCHAR2(1));
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');
INSERT INTO t1 VALUES ('e');

Desired result of an SQL statement on test:

abcde

The answer came from Tamil Selvan:

1 SELECT REPLACE(col1, ' ')
2 FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
3 FROM t1
4 START WITH col1 = (select min(col1) from t1) -- 'a'
5 CONNECT BY PRIOR col1 < col1
6 ORDER BY level DESC)
7* WHERE rownum = 1
SQL> /

REPLACE(COL1,'')
----------------------------------------
abcde

Here are the things that may be new to someone new to Oracle (especially Oracle 9 or 10):
- SYS_CONNECT_BY_PATH
- CONNECT BY

This is an interesting example, but it is just one use of these features.

Here is a link to Dan Morgan's reference on CONNECT BY
http://www.psoug.org/reference/connectby.html

And here is an Ask Tom article.
http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html

Comments:
Quiz time

name the version of Oracle that introduced connect by.

You will likely be surprised by the answer.
 
I started Oracle with version 8, which didn't have SYS_CONNECT_BY_PATH or many of these fancy CONNECT_BY_WHATEVER, but it did have simple START WITH and CONNECT BY. Although I didn't know many people who bothered to use it. So my guess is Oracle 8 or earlier.

Here is the answer (from Jonathan Lewis' site) for cheaters like me:
http://www.jlcomp.demon.co.uk/faq/connectby.html
 
The FAQ needs to be updated...

Keep guessing.
 
Here is a great article on string aggregation by Tim Hall:

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
 
Hi Robert.
is very short the result string.
what is the max long???
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?