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 Go to next message
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 #419391 is a reply to message #419373] Fri, 21 August 2009 15:02 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

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?
Re: how to return two values from a function call [message #419413 is a reply to message #419373] Fri, 21 August 2009 23:27 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
Quote:
Is there a way to retrieve both values without creating the function twice


I would recommend to use a procedure for the same. By using procedure you would be able to return one or more values.
Re: how to return two values from a function call [message #419417 is a reply to message #419391] Fri, 21 August 2009 23:54 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
well if the loop is taking abnormal time as you said:-

Quote:
The loop inside the function is running extremely too slow, Im talking 30+ mins


There is something wrong in your query structure. Just check that.
Re: how to return two values from a function call [message #419418 is a reply to message #419373] Fri, 21 August 2009 23:59 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #419626 is a reply to message #419373] Mon, 24 August 2009 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the exit condition for the loop?
Re: how to return two values from a function call [message #419632 is a reply to message #419626] Mon, 24 August 2009 10:13 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks for replying the exit for the loop is:
IF APPROX_TOTAL_HRS <= 250000 THEN EXIT; END IF;


This value is set before the loop begins and changes based on the new_end_date.
Re: how to return two values from a function call [message #419635 is a reply to message #419373] Mon, 24 August 2009 10:25 Go to previous messageGo to next message
cookiemonster
Messages: 12415
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 #419651 is a reply to message #419635] Mon, 24 August 2009 15:46 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

I wish there was a way around the loop Sad 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.

For example, if date was 8/24/2009 and hours were 331,000, then the loop will subtract one day from the loop until the hours is close to 250,000 hours as possible which can end with a date of 6/1/2009, where hours will equal 250,092.
Re: how to return two values from a function call [message #419734 is a reply to message #419373] Tue, 25 August 2009 06:43 Go to previous messageGo to next message
flyboy
Messages: 1832
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;
I do not understand, why are you using GROUP BY clause at all. It only brings the necessity of catching NO_DATA_FOUND exception and (as you filter LOCATION_ID, but group by LOCATION_NAME), there is possibility of TOO_MANY_ROWS exception happening (if there are multiple names for one LOCATION_ID). Seems like non-normalized table (if it is not a view).
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 #419768 is a reply to message #419734] Tue, 25 August 2009 09:26 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks so much for responding,

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. 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.

I will test this and see what happends, thanks.
Re: how to return two values from a function call [message #419775 is a reply to message #419768] Tue, 25 August 2009 09:55 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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. Smile 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 Go to previous messageGo to next message
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

Re: how to return two values from a function call [message #419903 is a reply to message #419373] Wed, 26 August 2009 02:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Thank you for feedback.
Previous Topic: Connect Oracle Report Builder 9.0.4.0.33 with Oracle 9i database
Next Topic: How Can Find Out That Doument is printed Successfully
Goto Forum:
  


Current Time: Wed Dec 07 16:12:35 CST 2016

Total time taken to generate the page: 0.08883 seconds