PSUEDO Column :Level and Connect By Clause in Oracle SQL

  PSUEDO Column

Level 

and Connect By Clause

                                                                              -----Documented by Venkat

Q: How to Implement Loop in Oracle SQL?

> "LEVEL" is a System Variable (PSUEDO Column)

> By default LEVEL initialized with 1

> By default LEVEL incremented by 1 

Ex:

Initially 

1

1+1=2

2+1=3

....etc.

SELECT  LEVEL

 FROM DUAL

 CONNECT BY LEVEL <= 10; 

--

Result:

LEVEL

1

2

3

4

5

6

7

8

9

10

--Q: Display numbers from 1 to 10 using Level and connect by?

select 

level

from 

dual

connect by level<=10;

--Q: Write a query to print even numbers upto 20?

select 

level 

from dual

where 

mod(level,2)=0

connect by level<=20;

--for ODD numbers

select 

level 

from dual

where 

mod(level,2)=1

connect by level<=20;

--Q: Write a query to print all ASCII Characters?

select

level-1, 

chr(level-1) 

from 

dual

connect by level<=256;

--Q: Write a query to print 2026 Calendar?

Method 1:

SELECT  LEVEL + AS DATEID,

                 TO_CHAR(LEVEL + TO_DATE( '31-DEC-25' ,'DD-MON-YY'),'DAY') AS DAY

  FROM DUAL

  CONNECT BY LEVEL<=366; 

 

Method 2:

 select 

 level+to_date('31-DEC-25' ,'DD-MON-YY') as date_id,

 TO_CHAR(LEVEL + to_date( '31-DEC-25' ,'DD-MON-YY'),'DAY') AS day

 from dual

 connect by level<=366;

 

-- Q: Input string and  print it in the following pattern

Input String :- VENKAT

Output should be :- 

V

E

N

K

A

T

Sol:

SELECT SUBSTR(:input_string, LEVEL, 1) AS output

FROM DUAL

CONNECT BY LEVEL <= LENGTH(:input_string);

Q: Input string and  print it in the following pattern

Input String :- VENKAT

Output should be :- 

V

VE

VEN

VENK

VENKA

VENKAT

SELECT SUBSTR('VENKAT', 1, LEVEL) AS Left_Stair

FROM DUAL

CONNECT BY LEVEL <= LENGTH('VENKAT');

Q:Write a query to display president to employee hierarchial data for emp table using level, connect by clause

SELECT 

    LPAD(' ', 3 * (LEVEL - 1)) || ENAME AS "Organizational Tree",

    JOB,

    EMPNO,

    MGR,

    LEVEL

FROM EMP

START WITH MGR IS NULL  -- The President has no manager

CONNECT BY PRIOR EMPNO = MGR

ORDER SIBLINGS BY ENAME;

Q: How to print the multiplication tables?

SELECT CASE

         WHEN MOD(LEVEL - 1, 10) = 0 THEN '--- Table of '

                                          || Ceil(LEVEL / 10)

                                          || ' ---'

         ELSE NULL

       END                                                AS header,

       Ceil(LEVEL / 10)

       || ' x '

       || ( MOD(LEVEL-1, 10) + 1 )

       || ' = '

       || ( Ceil(LEVEL / 10) * ( MOD(LEVEL-1, 10) + 1 ) ) AS ENTRY

FROM   dual

CONNECT BY LEVEL <= 200;