CODEWITHSUNDEEP
sqloracle-database
Rajshree Rai
Rajshree Rai

Reputation: 642

Alternative to calling same function twice on a select query

I have a select query for a view which is like

SELECT
    c.id,
    c.name,
    p.name   AS person_name,
    fetch_selected_student_of_class(c.id) as student,
    fetch_selected_section(fetch_selected_student_of_class(c.id), c.id) as section
    FROM
    class   c
    left JOIN persons                  p ON c.room_id = p.id

Here the function fetch_selected_student_of_class is repeated twice. So the function would be executed twice. Is there any way to avoid this?

Upvotes: 0

Views: 102

Answers (2)

Its_Ady
Its_Ady

Reputation: 336

You ca do something like this.

select *,fetch_selected_section(d.student, c.id) as section from 
(
SELECT
c.id,
c.name,
p.name   AS person_name,
fetch_selected_student_of_class(c.id) as student,

FROM
class   c
left JOIN persons                  p ON c.room_id = p.id
) d

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You can use a subquery:

SELECT c.id, c.name, p.name   AS person_name, c.student,
       fetch_selected_section(c.student, c.id) as section
FROM (SELECT c.*, fetch_selected_student_of_class(c.id) as student
      FROM class c
     ) c LEFT JOIN
     persons p
     ON c.room_id = p.id

Upvotes: 2

Related Questions