How to write a query with loop in Oracle

Question

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:

Can you please post some more details? It would be useful to know the exact structure of your tables, an example of your data and of the desired result — Aleksej
– Aleksej, Commented Feb 19, 2016 at 14:18
Your table seems to be very poorly designed. You have a composite data element in next_to_check and, apparently, next_to_check and next_to_check2 are different columns that store the same values. It would make much more sense (and make the query much easier to write) if you modeled this as a standard parent-child hierarchy table. — Justin Cave
– Justin Cave, Commented Feb 19, 2016 at 14:19
They way I see it you have two issues here: 1 you need the multiple values to be split into separate rows. Second you need to traverse a hierarchy. You could use table(cast(multiset( to split the , into multiple lines, and then use a recursive CTE, or a connect by prior to traverse hierachy — xQbert
– xQbert, Commented Feb 19, 2016 at 14:26

krokodilko · Accepted Answer · 2016-02-19 19:49:02Z

1

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.

answered Feb 19, 2016 at 19:49

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary_W Over a year ago

Consider using this REGEXP_SUBSTR() call in order to handle NULL list elements should they occur: regexp_substr(next_to_check, '(.*?)(,|$)', 1, LEVEL, NULL, 1). See this for more info as using the regex '[^,]+' to parse a list with NULL elements will get you into trouble: stackoverflow.com/questions/31464275/…. Granted this may not apply to this exact situation but you better be aware of the issue lest you return incorrect data without knowing it!

Collectives™ on Stack Overflow

How to write a query with loop in Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related