CODEWITHSUNDEEP
oracle-databaseplsql
Waldemar
Waldemar

Reputation: 65

How to write a query with loop in Oracle

How to write a query that loop perform the following task: (Substituting the id of individual results)

    SELECT * From Table where id= 24
    Id | next_to_check  |  next_to_check_2
     24|    34,23       | 4

Then we check what we displayed a means 34,23,4

    SELECT * From tablle where id = 34
    SELECT * From tablle where id = 23
    SELECT * From tablle where id = 4

Then substitute results 34,23,4 then the results with the results and deeper and deeper

24-> 34,23,4
34-> which results in a
23-> which results in a
4-> is as a result of

what a result -> result with the result and so on ...

when I do it manually, it looks like this:

enter image description here

Upvotes: 1

Views: 131

Answers (1)

krokodilko
krokodilko

Reputation: 36097

A query would be very simple if a table had been properly normalized.
As mentioned in the comments, there are two issues:

  • next_to_check and next_to_check2 are two columns that store the same values
  • both columns contains lists of values separated by commas, instead of individual values

The table should look like this:

SELECT * From Table where id= 24
    Id | next_to_check  | 
     24|    34          | 
     24|    23          |
     24|    4           |

where a type of next_to_check column must be the same as id column to avoid unnecessary casting.

For the above table the query may be just:

SELECT *
FROM "TABLE"
start with id = 24
connect by id = prior next_to_check;

If the table cannot be normalized, then you can normalize data "on the fly" using a query like this:

WITH normalized_data As (
    SELECT id, trim(regexp_substr(next_to_check, '[^,]+', 1, LEVEL)) next_to_check
    FROM "TABLE"
    CONNECT BY LEVEL <= regexp_count(next_to_check, ',')+1  
    UNION ALL
    SELECT id, trim(regexp_substr(next_to_check_2, '[^,]+', 1, LEVEL)) next_to_check
    FROM "TABLE"
    CONNECT BY LEVEL <= regexp_count(next_to_check_2, ',')+1 
)
SELECT * FROM normalized_data

and then glue the first query to the above query:

WITH normalized_data As (
    SELECT id, trim(regexp_substr(next_to_check, '[^,]+', 1, LEVEL)) next_to_check
    FROM "TABLE"
    CONNECT BY LEVEL <= regexp_count(next_to_check, ',')+1  
    UNION ALL
    SELECT id, trim(regexp_substr(next_to_check_2, '[^,]+', 1, LEVEL)) next_to_check
    FROM "TABLE"
    CONNECT BY LEVEL <= regexp_count(next_to_check_2, ',')+1 
)
SELECT * FROM normalized_data
start with id = 24
connect by id = prior next_to_check;

but a performance of this "workaround" will be poor, it may work for 100 or 1000 records, but it take years on a bigger table.

Upvotes: 1

Related Questions