1

I have a query as below

select substr( 'ORACLE DEVELOPER',level,1 ) ITEM
from dual connect by level <= length('ORACLE DEVELOPER') ;

it returns 'O' as the result.

but if i put this as a subquery in other it is displaying the required output. query is as below.

select a.ITEM from (select substr( 'ORACLE DEVELOPER',level,1 ) ITEM
from dual connect by level <= length('ORACLE DEVELOPER') ) a

How this CONNECT BY is working in subquery. I am new to this feature. Can anyone explain this??

Incognito
  • 2,964
  • 2
  • 27
  • 40
user3354255
  • 11
  • 2
  • 2
    Both produce same [result](http://sqlfiddle.com/#!4/d41d8/25887). – Noel Feb 26 '14 at 05:13
  • possible duplicate of [Why does a connect by expression in a FOR loop, execute only once?](http://stackoverflow.com/questions/22815549/why-does-a-connect-by-expression-in-a-for-loop-execute-only-once) – Ben May 07 '14 at 20:36

1 Answers1

2

CONNECT BY is Oracle SQL syntax for doing hierarchical queries. Some good examples are here.

One of the side effects is that it allows you to query a single data source multiple times, which means that there's a handy trick to generate any number of rows with:

SELECT 1
FROM dual
CONNECT BY LEVEL <= 10;

The above will connect dual back to itself 10 times - the LEVEL pseudocolumn is from the hierarchy; because the CONNECT BY clause doesn't actually refer to any data from the rowsource, it effectively makes dual its own child, grandchild, great-grandchild, etc. until the CONNECT BY clause evaluates to false (in this case, when LEVEL becomes greater than 10).

In your case, you are generating a row for each letter of the string (instead of 10, you are referring to LENGTH('a string'), which is a nice way of getting a query to return one record for each letter in the string. You are then using SUBSTR to pick out the nth letter from the string.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158