Home » Developer & Programmer » Reports & Discoverer » how to return two values from a function call (report builder 6i)
| how to return two values from a function call [message #419373] |
Fri, 21 August 2009 10:52  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Hello all,
I am calling a function from the database to a report but I need two values that the function calculates. Is there a way to retrieve both values without creating the function twice. I am looking for something like a global variable perhaps.
Example
FCTN MANHOURS(pLocation NUMBER, pBeginDate DATE, pEndDate DATE) RETURN NUMBER
-- I am returning total hours but I also need to return the pEndDate, this value also changes in the function.
Any help is appreciated.
|
|
|
|
|
|
|
|
|
|
| Re: how to return two values from a function call [message #419418 is a reply to message #419373] |
Fri, 21 August 2009 23:59  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| deahayes3 wrote on Fri, 21 August 2009 17:52 | Is there a way to retrieve both values without creating the function twice. I am looking for something like a global variable perhaps.
|
This does not look to be a good idea. There are following ways, how to pass multiple values from a subprogram:
- return OBJECT type consisting of both NUMBER and DATE
- use OUT parameter - but then it would not be possible to call this function from SQL query
| deahayes3 wrote on Fri, 21 August 2009 22:02 | I also have another problem. The loop inside the function is running extremely too slow, Im talking 30+ mins. I have tried using select into, union and I even moved it to a function on database (see previous post problem) but it still runs slow, any suggestion on how to speed this up?
|
I am afraid, that without at least rough description, what this function does (LOOP? over what? is it necessary? who may know it from your post?) and how it is used, there will be no reasonable suggestions. Maybe somebody with mind reading skills may prove me wrong though.
You specified elapsed time. But, without adding, how many LOOP cycles were done in average and how many rows were processed, this has no meaning - its performance may be good.
[Edit: Added the last paragraph]
[Updated on: Sat, 22 August 2009 00:06] Report message to a moderator |
|
|
|
| Re: how to return two values from a function call [message #419614 is a reply to message #419418] |
Mon, 24 August 2009 08:23 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Thanks everyone for your responses. The loop calculates number of hours worked using the sum function on 5 different tables then adds the results together, here is a snippet of the code.
LOOP
NEW_END_DATE:= NEW_END_DATE -1;
begin
SELECT SUM(NVL(empl_hours,0))
INTO v_hours
FROM empl_proj_time_history
WHERE location_id = :P_location_id
AND empl_date between :p_begin_date AND NEW_END_DATE
AND NVL(user_person_type,'EMPLOYEE') <> 'Outside Contractor'
GROUP BY location_name;
EXCEPTION
WHEN no_data_found THEN
v_hours:=0;
end;
begin
SELECT SUM(NVL(empl_hours,0))
INTO v_hours2
FROM empl_proj_time
WHERE location_id = :P_location_id
AND empl_date between :p_begin_date AND NEW_END_DATE
AND NVL(user_person_type,'EMPLOYEE') <> 'Outside Contractor'
GROUP BY location_name;
EXCEPTION
WHEN no_data_found THEN
v_hours2:=0;
end;
Begin
SELECT SUM(NVL(empl_hours,0))
INTO v_hours3
FROM empl_proj_time_history
WHERE location_id = :P_location_id
AND empl_date between p_date_from AND :p_end_date
AND NVL(user_person_type,'EMPLOYEE') = 'Outside Contractor'
GROUP BY location_name;
end date;
--2 more tables.....
IF APPROX_TOTAL_HRS <= 250000 THEN EXIT; END IF;
APPROX_TOTAL_HRS:= v_hours2 + v_hours+ v_hours3(--others tables here);
END LOOP;
RETURN(APPROX_TOTAL_HRS);
[Updated on: Mon, 24 August 2009 09:16] Report message to a moderator |
|
|
|
|
|
|
|
| Re: how to return two values from a function call [message #419635 is a reply to message #419373] |
Mon, 24 August 2009 10:25 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I would have thought you'd run the risk of an infinite loop with just that condition.
As for speeding it up - you need to work out which queries are slow - so time each one.
Then have a look at the explain plan for the slow ones.
Have a look at the sticky at the top of the performance tuning forum for information on what to look for.
It might also be possible to do away with the loop but you'll have to tell us what you're trying to achieve with it.
|
|
|
|
|
|
| Re: how to return two values from a function call [message #419734 is a reply to message #419373] |
Tue, 25 August 2009 06:43 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
I do not know, how to get rid of PL/SQL efficiently; anyway your code has some place for improvements:
LOOP
NEW_END_DATE:= NEW_END_DATE -1;
<statements>
IF APPROX_TOTAL_HRS <= 250000 THEN EXIT; END IF;
APPROX_TOTAL_HRS:= v_hours2 + v_hours+ v_hours3(--others tables here);
END LOOP;
Here, you are making one extra call of <statements> before leaving. If the only intention is to get correct date, simply decrease it after the loop.
begin
SELECT SUM(NVL(empl_hours,0))
INTO v_hours
FROM empl_proj_time_history
WHERE location_id = :P_location_id
AND empl_date between :p_begin_date AND NEW_END_DATE
AND NVL(user_person_type,'EMPLOYEE') <> 'Outside Contractor'
GROUP BY location_name;
EXCEPTION
WHEN no_data_found THEN
v_hours:=0;
end;
| Quote: | then the loop will subtract one day from the loop until the hours is close to 250,000 hours as possible
|
Without knowing data (number of days, rows per day, usual conditions and indexes), it is hard to suggest, but I would rather add days until the sum reaches the limit instead of decreasing.
Or, maybe better, query it once grouped by days and sum it from result lists, something like (not tested): SELECT empl_date, SUM(NVL(empl_hours,0))
BULK COLLECT INTO v_dates, v_hours
FROM empl_proj_time_history
WHERE location_id = :P_location_id
AND empl_date between :p_begin_date AND <starting new_end_date>
AND NVL(user_person_type,'EMPLOYEE') <> 'Outside Contractor'
GROUP BY empl_date -- if empl_date contains time part, you will need to truncate it
ORDER BY empl_date;
v_sum := 0
FOR i in 1..v_hours.count LOOP
v_sum := v_sum+v_hours(i);
if v_sum >= 250000 then
RETURN v_sum;
end if;
END LOOP;
RETURN v_sum; |
|
|
|
|
|
| Re: how to return two values from a function call [message #419775 is a reply to message #419768] |
Tue, 25 August 2009 09:55 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| deahayes3 wrote on Tue, 25 August 2009 16:26 | I am going to try the latter of your statement and create the for loop. I tried testing this in report builder except that it does not like "BULK COLLECT" and the v_hours.count.
|
It might happen if you did not change the type of V_HOURS.
As you did not post it, I did not post it so; expecting, you know, it must be collection type.
| deahayes3 wrote on Tue, 25 August 2009 16:26 | I removed bulk collect and I am thinking of placing the SELECT INTO in a cursor and using ROWCOUNT since v_hours.count does not work in this for loop.
|
Welcome back to the (slightly different) LOOP.
|
|
|
|
| Re: how to return two values from a function call [message #419781 is a reply to message #419775] |
Tue, 25 August 2009 10:27 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Good news, the report is now only taking < 4 mins to generate unlike before when it was 30mins.  The only problem I am running into now is with the Order by(which I need in order to sum correctly). Since I have to pull from more than one table for the hours(stated in a previous post) I use union all, I had to comment out order by and if I put it at the bottom of the query it works in PL/SQL however it does not work in ReportBuilder. I get an error that says :
"ORDER BY must be the number of a Select-list expression" I've seen this before, what does it mean? Here is snippet of Union All statement :
SELECT epth.empl_date, SUM(NVL(epth.empl_hours,0))
FROM EMPL_PROJ_TIME_HISTORY EPTH
WHERE NVL(EPTH.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTH.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EPTH.USER_PERSON_TYPE <> 'Outside Contractor'
GROUP BY epth.empl_date
--ORDER BY epth.empl_date
UNION ALL
SELECT eptw.empl_date, SUM(NVL(eptw.empl_hours,0))
FROM EMPL_PROJ_TIME EPTW
WHERE NVL(EPTW.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTW.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN p_date_from AND new_end_date
AND EPTW.EMPL_DATE > '01-JAN-2006'
AND EPTW.USER_PERSON_TYPE = 'Outside Contractor'
GROUP BY eptw.empl_date
--ORDER BY eptw.empl_date
UNION ALL
SELECT epth.empl_date, SUM(NVL(epth.empl_hours,0))
FROM EMPL_PROJ_TIME_HISTORY EPTH
WHERE NVL(EPTH.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTH.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND epth.empl_date BETWEEN p_date_from AND new_end_date
AND EPTH.EMPL_DATE > '01-JAN-2006'
AND EPTH.USER_PERSON_TYPE = 'Outside Contractor'
GROUP BY epth.empl_date
--ORDER BY epth.empl_date
UNION ALL
SELECT ept.empl_date, SUM(NVL(ept.empl_hours,0))
FROM EMPL_PROJ_TIME_01 EPT
WHERE NVL(EPT.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPT.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EPT.USER_PERSON_TYPE <>'Outside Contractor'
GROUP BY ept.empl_date
--ORDER BY ept.empl_date
UNION ALL
SELECT ep.empl_date, SUM(NVL(ep.empl_hours,0))
FROM EMPL_PROJ_TIME_99 EP
WHERE NVL(EP.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EP.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EP.USER_PERSON_TYPE <> 'Outside Contractor'
GROUP BY ep.empl_date
ORDER BY ep.empl_date ;
|
|
|
|
| Re: how to return two values from a function call [message #419786 is a reply to message #419781] |
Tue, 25 August 2009 10:37 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
I hit the reply button too soon on this one, I figured it out. I just put the whole query (which is in a cursor by the way) into a select all, and it is WORKING! Thanks for all your help!
SELECT * FROM (
SELECT epth.empl_date, SUM(NVL(epth.empl_hours,0))
FROM EMPL_PROJ_TIME_HISTORY EPTH
WHERE NVL(EPTH.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTH.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EPTH.USER_PERSON_TYPE <> 'Outside Contractor'
GROUP BY epth.empl_date
--ORDER BY epth.empl_date
UNION ALL
SELECT eptw.empl_date, SUM(NVL(eptw.empl_hours,0))
FROM EMPL_PROJ_TIME EPTW
WHERE NVL(EPTW.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTW.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN p_date_from AND new_end_date
AND EPTW.EMPL_DATE > '01-JAN-2006'
AND EPTW.USER_PERSON_TYPE = 'Outside Contractor'
GROUP BY eptw.empl_date
--ORDER BY eptw.empl_date
UNION ALL
SELECT epth.empl_date, SUM(NVL(epth.empl_hours,0))
FROM EMPL_PROJ_TIME_HISTORY EPTH
WHERE NVL(EPTH.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPTH.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND epth.empl_date BETWEEN p_date_from AND new_end_date
AND EPTH.EMPL_DATE > '01-JAN-2006'
AND EPTH.USER_PERSON_TYPE = 'Outside Contractor'
GROUP BY epth.empl_date
--ORDER BY epth.empl_date
UNION ALL
SELECT ept.empl_date, SUM(NVL(ept.empl_hours,0))
FROM EMPL_PROJ_TIME_01 EPT
WHERE NVL(EPT.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EPT.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EPT.USER_PERSON_TYPE <>'Outside Contractor'
GROUP BY ept.empl_date
--ORDER BY ept.empl_date
UNION ALL
SELECT ep.empl_date, SUM(NVL(ep.empl_hours,0))
FROM EMPL_PROJ_TIME_99 EP
WHERE NVL(EP.HOL_VAC_TAG,'PROJECTS') IN ('PROJECTS','WD','WH','WT')
AND EP.PROCESS_CODE <> 'A' AND location_id = :P_location_id
AND empl_date BETWEEN :p_begin_date AND new_end_date
AND EP.USER_PERSON_TYPE <> 'Outside Contractor'
GROUP BY ep.empl_date [COLOR=crimson])[/COLOR]ORDER BY empl_date ;
[Updated on: Tue, 25 August 2009 10:39] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 08 20:20:55 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
which I believe is why program is running so long. The loop is to find the date where the sum of hours is close to 250,000 hours starting with an inital date.