MaterialGirl MaterialGirl - 1 month ago 14
SQL Question

Rewrite query in FOR LOOP to single query

Could you please help me to rewrite the following query inside of FOR LOOP query to single query without loop:

for rec in select distinct sp.student_id, v.test_id, v.name, p.version_id from student_pages sp, pages p, versions v where p.id = sp.page_id and v.id = p.version_id order by student_id, test_id, name LOOP
select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index) as lost_page_indices into l_lost_page_indices from pages p left join student_pages sp on p.id = sp.page_id and sp.student_id = rec.student_id where p.version_id = rec.version_id and sp.page_id is null;
end loop;


In the final query I need the following fields: sp.student_id, v.test_id, v.name and lost_page_indices.

Answer

First cut: Take the query you're looping over, turn it into a subquery, and use it as a join table.

select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index)
       as lost_page_indices into l_lost_page_indices
from pages p
join (
     select distinct sp.student_id, v.test_id, v.name, p.version_id
     from student_pages sp
     join pages p on p.id = sp.page_id
     join versions v on v.id = p.version_id
) as rec on rec.student_id = sp.student_id
left join student_pages sp on p.id = sp.page_id and
                              sp.student_id = rec.student_id
where p.version_id = rec.version_id and
      sp.page_id is null
order by rec.student_id, rec.test_id, rec.name

I reorgnaized the subquery using join syntax for easier reading.

order by cannot be relied on to survive a join, so it moves into the outer query.

There's no group by so I'm not sure if an aggregate function will work.

And, as others have pointed out, string_agg() is not a built in Oracle function. You have to make it yourself.


That can be simplified, there's a lot of redundancy between the two joins. That subquery is putting together student_pages, pages, and versions which can all be done with a normal join. The only thing left is the distinct sp.student_id which can be better done with a group by sp.student_id.

select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index)
      as lost_page_indices into l_lost_page_indices
from pages p
left join student_pages sp on sp.page_id = p.id
join versions v on v.id = p.version_id
where sp.page_id is null
group by sp.student_id
order by sp.student_id, v.test_id, v.name

I'm not 100% sure that's an equivalent query, but it should get you started. This makes it a lot clearer what the query is doing: find orphaned pages and stick them into a table.