Skip to Main Content
  • Questions

  • SYS_CONNECT_BY_PATH function limitations

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, PUNIT.

Asked: June 09, 2008 - 5:04 pm UTC

Last updated: January 15, 2013 - 2:39 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,
We are using SYS_CONNECT_BY_PATH function to get all possible combination of a column having quantity.
Problem we are facing is , it is not considering the repeated values for that column like if there are values like 30,15,15,20
possible combinations would be

Combinations with 2 values
30 - 15 (1st 15)
30 - 15 (2nd 15)
30 - 20
15 (1st 15) - 15 (2nd 15)
15 (1st 15) - 20
15 (2nd 15) - 20

Combinations with 3 values
30 , 15 (1st 15) ,15 (2nd 15)
30 , 15 (1st 15), 20
30 , 15 (2nd 15), 20
15 (1st 15) ,15 (2nd 15), 20

Combinations with 4 values
30 , 15 (1st 15) ,15 (2nd 15), 20

So ideally these combinations are possible. But query

select  sys_connect_by_path(QTY, ',' ) colvalues 
   from (select '('||TO_NUMBER(QTY)||')' QTY 
              from TABLE X)
      connect by QTY  > prior QTY


is givng me follwoing output.

,(30)
,(20)
,(20),(30)
,(-15)
,(-15),(30) -- 1st 15
,(-15),(20) -- 1st 15
,(-15),(20),(30) -- 1st 15
,(-15)
,(-15),(30) -- 2nd 15
,(-15),(20) -- 2nd 15
,(-15),(20),(30) -- 2nd 15


So question here is , why it is not giving me combination
30 , -15 , - 15 or -15 , -15 or -15, -15, 20

as neither in any of the combination both -15 are present.

and Tom said...

because you said "qty > prior qty"

15 = 15

15 is not greater than or less than 15, I don't know why you would expect that to be there given the predicate you applied.

This is not going to work with connect by. You want to connect each row to every other row such that the qty >= prior qty and we haven't already connected to that row in the hierarchy 

ops$tkyte%ORA10GR2> select qty, sys_connect_by_path( qty, ',' ) scbp
  2    from (
  3  select qty, row_number() over (order by qty) rn
  4    from t
  5         )
  6   connect by prior qty >= qty and rn < prior rn
  7   order by level
  8  /

       QTY SCBP
---------- ------------------------------
        15 ,15
        15 ,15
        30 ,30
        20 ,20
        15 ,20,15
        15 ,20,15
        15 ,30,15
        15 ,15,15
        20 ,30,20
        15 ,30,15
        15 ,30,15,15
        15 ,30,20,15
        15 ,30,20,15
        15 ,20,15,15
        15 ,30,20,15,15

15 rows selected.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Limitations of SYS_CONNECT_BY_PATH

prakash, January 15, 2013 - 12:26 pm UTC

Tom,

If there are more columns in a table say, 240 columns and I want to make a csv list of all the columns from dba_tab_columns, i am getting error ORA-01489. But for tables with columns less than 50, it is working good. Is there any other way to resolve this? please help.

I used this query.

SELECT SYS_CONNECT_BY_PATH (COLUMN_NAME , ',') csv
FROM (SELECT COLUMN_NAME,ROW_NUMBER () OVER (ORDER BY S.COLUMN_NAME ) rn,
COUNT (*) OVER () cnt
FROM dba_tab_columns s
WHERE s.TABLE_NAME = 'SECURITY_HOLDING'
AND s.owner = 'AKS')
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
Tom Kyte
January 15, 2013 - 2:39 pm UTC

sys connect by path is limited currently to 4000 bytes

you can use stragg (my function) with a clob

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402

search for clob on that page for the clob implementation
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.