Is there any easy way to make use of this function?

From: <dennis.pong_at_gmail.com>
Date: 26 May 2006 11:21:10 -0700
Message-ID: <1148667670.295449.264620@j33g2000cwa.googlegroups.com>

Hi,

I discovered a rounding issue with the floor function with PL/SQL, so what happened was 00:08:00 (hh:mm:ss) and 00:08:60 (hh:mm:ss) both existed in my table. The original script to pull this TPP metric was

to_char(FLOOR((time_per_person)/3600),'09') || ':' ||
       to_char(FLOOR(mod((time_per_person),3600)/60),'09') || ':' ||
       to_char(mod(mod((time_per_person),3600), 60),'09')||'    '

time_per_person,
(time_per_person was a number )

So I digged into the code somewhere and found a procedure in a package.

PROCEDURE get_time_person(p_duration in number, p_exception_required in boolean default false, p_time_person out nocopy varchar2) IS

v_tmp   number;
v_hour  number;
v_min   number;
v_sec   number;

BEGIN
	if p_exception_required or p_duration < 0 then
		p_time_person := 'N/A';
	elsif p_duration = 0 or p_duration is null then
		p_time_person := '--';
    else
		v_hour := FLOOR(p_duration / 3600);
		v_tmp  := p_duration - v_hour * 3600;
		v_min  := FLOOR(v_tmp / 60);
		IF v_min = 60 THEN
			v_hour := v_hour + 1;
			v_min := 00;
		END IF;
		v_sec  := v_tmp MOD 60;
		IF ROUND(v_sec) = 60 THEN
			v_min := v_min + 1;
			v_sec := 00;
		END IF;
		p_time_person := TO_CHAR(v_hour, '99999') || ':' ||
LTRIM(TO_CHAR(v_min, '09')) || ':' || LTRIM(TO_CHAR(v_sec, '09'));
	end if;
EXCEPTION
	WHEN OTHERS THEN
		p_time_person := 'N/A';

END get_time_person;

And I changed it to a function like the following

CREATE FUNCTION get_time_person (

   p_duration IN NUMBER
   p_exception_required IN BOOLEAN DEFAULT FALSE )

   RETURN VARCHAR2
AS

   v_tmp           NUMBER;
   v_hour          NUMBER;
   v_min           NUMBER;
   v_sec           NUMBER;
   p_time_person   VARCHAR2 (100);

BEGIN
   IF p_exception_required OR p_duration < 0    THEN
      p_time_person := 'N/A';
   ELSIF p_duration = 0 OR p_duration IS NULL    THEN
      p_time_person := '--';
   ELSE

      v_hour := FLOOR (p_duration / 3600);
      v_tmp := p_duration - v_hour * 3600;
      v_min := FLOOR (v_tmp / 60);

      IF v_min = 60
      THEN
         v_hour := v_hour + 1;
         v_min := 00;
      END IF;

      v_sec := v_tmp MOD 60;

      IF ROUND (v_sec) = 60
      THEN
         v_min := v_min + 1;
         v_sec := 00;
      END IF;

      p_time_person :=
            TO_CHAR (v_hour, '99999')


         || ':'

         || LTRIM (TO_CHAR (v_min, '09'))

         || ':'

         || LTRIM (TO_CHAR (v_sec, '09'));

   END IF;    RETURN p_time_person;
EXCEPTION
   WHEN OTHERS
   THEN
      p_time_person := 'N/A';

END get_time_person;

So, my question is, how would I go about using this function above incorporating with create table (+ nested select statements, which I didn't paste here) ?
Is it true that I am not allowed to call user-defined functions with a select statement ? Received on Fri May 26 2006 - 13:21:10 CDT