CODEWITHSUNDEEP
sqloracle-database
Kenny
Kenny

Reputation: 1982

CONNECT BY without PRIOR

I came across a query to create a dummy table like this 

CREATE TABLE destination AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 10;

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

10 rows selected.

Could you share some insights of how this works ? First, the missing of PRIOR is unbeknownst to me. Second, I don't get how the tree is constructed. From the level it looks like they are all branched out from the same root.

Upvotes: 0

Views: 1147

Answers (1)

user5683823
user5683823

Reputation:

This gimmick was noticed by a DB professional, Mikito Harakiri, and shared on AskTom. It has been adopted in the Oracle community, although it is undocumented (it actually goes against the documentation), and its use is somewhat dangerous in that Oracle may at some point make it no longer work. (Although with its already massive use, it would be insane for Oracle to take it back.)

The rows are indeed branching from the same root, the single row of dual. You can use any other table that has EXACTLY ONE row for the same trick. If you start with two rows (or you use the trick on your own table, with many rows), you will quickly run into trouble. There are ways around that, you will pick that up over time. You may be interested in following the Oracle forum, at OTN, people use this trick all the time there.

Here is an article that discusses this trick: http://www.sqlsnippets.com/en/topic-11821.html

Upvotes: 4

Related Questions