Use the UDF?

[lespaul]

I've created a small little function to take an integer time value and convert it to an ACTUAL time value:

CREATE FUNCTION QGPL.INTTOTIME (
	IntTime INTEGER)
	RETURNS Time
	LANGUAGE SQL
	SPECIFIC QGPL.INTTOTIME 
	BEGIN
	DECLARE TempRes Char(5);
	DECLARE FinalTemp Char(5);	
	set TempRes = Char(IntTime);

	If length(TempRes) = 3 Then
		set TempRes = '0' || TempRes;
	End If;
	set FinalTemp = SUBSTRING(TempRes, 1, 2) || ':' || SUBSTRING(TempRes, 2, 2);

	Return FinalTemp;
	END;

The process is run and I have a function INtToTime in the QGPL library in the iSeries Navigator. However, when I try to USE the function I get a message:

INTTOTIME in *LIBL type *N not found.

What do I need to do in order to be able to USE the function? I have checked the permissions and have PUBLIC set to 'All' at the moment...any ideas?

thanks for any info!

Leslie

 

[Mercury2]

I think that this function can't work since the return value isn't a valid representation of a time value. A valid character representation of a time value according to the SQL Manual is HH:MM:SS, i.e 8-char field.
Here's what I should do.

CREATE FUNCTION QGPL.INTTOTIME ( IntTime INTEGER )                
    RETURNS Time                                             
    LANGUAGE SQL                                             
    BEGIN                                                    
    DECLARE TempRes Char(5);                                 
    DECLARE FinalTemp Char(8);                               
    set TempRes = Char(IntTime);                             
                                                             
    If length(TempRes) = 3 Then                              
        set TempRes = '0' || TempRes;                        
    End If;                                                  
    set FinalTemp = SUBSTRING(TempRes, 1, 2) || ':' ||       
                    SUBSTRING(TempRes, 3, 2) || ':' || '00'; 
                                                             
    Return TIME(FinalTemp);                                  
    END;

 

[MarcLodge]

I'll agree with Mercury. It seems that the function 'Returns Time' and what is being passed back is patently not the time.

Could you not do this in SQL without going to all this trouble?

Marc