Looping In Oracle SQL Using Level

We will see about the syntax LEVEL in Oracle sql. If we want to loop through and print a set of values in SQL, LEVEL could come handy.

Example query:

SELECT 'hi', LEVEL FROM DUAL
CONNECT BY LEVEL <= 5;

output:

hi 1
hi 2
hi 3
hi 4
hi 5

CONNECT BY LEVEL syntax does the trick here of incrementing the levels for looping till the value 5 is reached. So for above example, the SELECT columns will be fired 5 times as you can see in the output. The keyword LEVEL can be used in SELECT statement too, to print the current level, as you can see in the output.

There are so many interesting analytical applications of LEVEL in DUAL

SELECT SUBSTR('bala',1,LEVEL) LETTER FROM DUAL
CONNECT BY LEVEL <= LENGTH('bala');

output:

b
ba
bal
bala

SELECT SUBSTR('bala',LEVEL,1) LETTER FROM DUAL
CONNECT BY LEVEL <= LENGTH('bala');

output:

b
a
l
a

SELECT RPAD(SUBSTR('Bala',LEVEL,1), LENGTH('Bala') + 2 - level, '*')
FROM DUAL
CONNECT BY LEVEL <= LENGTH('Bala');

output

B****
a***
l**
a*

The requirement for below example is - convert Case (flipflop - upper to lower and viceversa) for given input. It can be achieved using different ways in Oracle. I have tried with LEVEL and LISTAGG. LISTAGG aggregates data. Please check for it online. I will add a post on LISTAGG shortly.

SELECT LISTAGG(changed,'') WITHIN GROUP (ORDER BY lvl) finally_changed
FROM
(
    SELECT lvl, CASE WHEN letter = UPPER(letter) THEN LOWER(letter)
    ELSE UPPER(letter) END changed
    FROM
    (
         SELECT LEVEL lvl,SUBSTR('bAlA',LEVEL,1) LETTER FROM DUAL
         CONNECT BY LEVEL <= LENGTH('bAlA')
    )
);

The query structure comprises of subqueries, please understand and run the queries starting from the inner most query.

The inner most query - query 3 is same as we have seen earlier - it splits given string into rows of letters.

The second query - query 2 has the main logic to convert Upper case to lower and vice versa using CASE.

The First main query does the regrouping process using LISTAGG function

Input: bAlA

output: BaLa

This is a basic post about LEVEL. Please add on or correct any mistakes. Welcome your suggestions. Thanks for your time.