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;
