Hi,
This is a simplified example of the query I'm trying to write.
I have two tables - PIPELINE (which includes columns CREDIT_RECEIVER and CREATOR) and EMPLOYEES (which includes columns EMAIL and LOB).
I want to write a query which joins PIPELINE.CREDIT_RECEIVER with EMPLOYEES.EMAIL to return LOB as "CREDIT_RECEIVER_LOB".
I also want this query to join PIPELINE.CREATOR with EMPLOYEES.EMAIL to return LOB as "CREATOR_LOB".
Currently, I am doing a left outer join on EMPLOYEES.EMAIL = PIPELINE.CREDIT_RECEIVER to get "CREDIT_RECEIVER_LOB", and calling a function GET_LOB(PIPELINE.CREATOR) (defined below) to get "CREATOR_LOB".
Query:
Select PIPELINE.ID as ID,
PIPELINE.CREDIT_RECEIVER as CREDIT_RECEIVER,
PIPELINE.CREATOR as CREATOR,
EMPLOYEES.LOB as CREDIT_RECEIVER_LOB,
GET_LOB(PIPELINE.CREATOR) as CREATOR_LOB
FROM PIPELINE
LEFT OUTER JOIN EMPLOYEES ON PIPELINE.CREDIT_RECEIVER=EMPLOYEES.EMAIL
Is there a more efficient way to write this query? This query is so slow that it usually times out on me.
Thanks,
Forrest
GET_LOB definition
create or replace function "GET_LOB"
(vemail in VARCHAR2)
return VARCHAR2
is
begin
DECLARE vLOB VARCHAR2(30);
BEGIN
SELECT LOB INTO vLOB FROM EMPLOYEES WHERE email = vemail;
return vLOB;
END;
end;