Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    16

    Unanswered: Calculating the fractional part of a day

    Hi,

    I need to create a UDF that returns a fraction of a day. I pass a parameter in the format hhmmss, where h represents hours, mm represents minutes, ss represents seconds, then 360000 would correspond to 36 hours, 00 minutes and 00 seconds. The return for this value would be 1.5 days, however only returns the integer part.

    Code:
    CREATE FUNCTION "GNR"."F_FRACDAY" ( TIMEFORMATVALUE INTEGER )
    	RETURNS DECIMAL
    	NO EXTERNAL ACTION
    
    F1: BEGIN ATOMIC
    
    	DECLARE HS DECIMAL; 
    	DECLARE MS DECIMAL; 
    	DECLARE SCS DECIMAL; 
    	DECLARE TOTAL DECIMAL;
    
    	DECLARE TIMESTR VARCHAR(6);
    	
    
    	SET TIMESTR = LPAD(TIMEFORMATVALUE,6,'0');
    
    	SET HS = DECIMAL(SUBSTR(TIMESTR,1,2));
    	SET MS = DECIMAL(SUBSTR(TIMESTR,3,2)); 
    	SET SCS = DECIMAL(SUBSTR(TIMESTR,5,2));
    	
    	SET TOTAL = (HS/24) + (MS/1440) + (SCS/84000);
    	
    	return TOTAL;
    
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you declare your variables with 0 scale, how do you expect to get fractional numbers?

  3. #3
    Join Date
    Mar 2012
    Posts
    16
    Thanks!!!!!!!!

    Quote Originally Posted by n_i View Post
    If you declare your variables with 0 scale, how do you expect to get fractional numbers?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The common programming practices(SQL wouldn't be an exception.)
    which were related to your function-body would be...
    (1) Don't declare unnecesary intermediate result variables, if it was used once.
    In your function-body:
    - all variables are useless.

    (2) Utilize languages capabilities related to each datatype.
    In your function-body:
    - use compound mathematical expressions, considering priority of operators.
    - use builtin-functions in DB2 SQL.
    - utilize the difference of handling of fractions between integer datatype and decimal datatype.
    - so on...

    Note: I learned these general practices(and more) in my early study of Assembler and PL/I languages.

    As a result, an example of revised function-body might be
    Code:
    CREATE FUNCTION GNR.F_FRACDAY( time_format INTEGER )
    RETURNS DECIMAL(9 , 6)
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
          time_format          / 10000 /    24.
    + MOD(time_format , 10000) /   100 /  1440.
    + MOD(time_format ,   100)         / 84000.
    ;
    Last edited by tonkuma; 03-25-12 at 15:02.

  5. #5
    Join Date
    Mar 2012
    Posts
    16

    Question

    Thanks for your recommendations. I'll try to apply it.
    Now I have another problem. Can I use a UDF in a group by clause?

    I am using the following statement:

    Code:
    select
    COUNT(*) as qtd,
    GNR.F_DATEDIFF2(TIMESTAMP1,TIMESTAMP2) F_DATEDIFF2
    from
    GNR.TABLE1 T
    group by
    GNR.F_DATEDIFF2(TIMESTAMP1,TIMESTAMP2)
    My udf:

    Code:
    CREATE FUNCTION GNR.F_DATEDIFF2(t1 TIMESTAMP, t2 TIMESTAMP) 
    RETURNS DECIMAL(20,10)
    
    RETURN  
    (
    DECIMAL(( DAYS(t1) - DAYS(t2)))  + 
    DECIMAL(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))/(86400.)
    )
    Thanks.
    Davis

    Quote Originally Posted by tonkuma View Post
    The common programming practices(SQL wouldn't be an exception.)
    which were related to your function-body would be...
    (1) Don't declare unnecesary intermediate result variables, if it was used once.
    In your function-body:
    - all variables are useless.

    (2) Utilize languages capabilities related to each datatype.
    In your function-body:
    - use compound mathematical expressions, considering priority of operators.
    - use builtin-functions in DB2 SQL.
    - utilize the difference of handling of fractions between integer datatype and decimal datatype.
    - so on...

    Note: I learned these general practices(and more) in my early study of Assembler and PL/I languages.

    As a result, an example of revised function-body might be
    Code:
    CREATE FUNCTION GNR.F_FRACDAY( time_format INTEGER )
    RETURNS DECIMAL(9 , 6)
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
          time_format          / 10000 /    24.
    + MOD(time_format , 10000) /   100 /  1440.
    + MOD(time_format ,   100)         / 84000.
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can I use a UDF in a group by clause?
    Yes You Can!

    But, it should be DETERMINISTIC and NO EXTERNAL ACTION.(Defaults are NOT DETERMINISTIC and EXTERNAL ACTION.)
    Please see my example of "CREATE FUNCTION GNR.F_FRACDAY".

    Here is more detailed descriptions.
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows
    group-by-clause

    Code:
                 .-,-----------------------.   
                 V                         |   
    >>-GROUP BY----+-grouping-expression-+-+-----------------------><
                   +-grouping-sets-------+     
                   '-super-groups--------'
    The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R.
    R is the result of the previous clause of the subselect.

    In its simplest form, a GROUP BY clause contains a grouping expression.
    A grouping expression is an expression used in defining the grouping of R.
    Each expression or column name included in grouping-expression must unambiguously identify a column of R (SQLSTATE 42702 or 42703).
    A grouping expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822),
    or any expression or function that is not deterministic or has an external action (SQLSTATE 42845).
    Last edited by tonkuma; 03-26-12 at 00:59.

  7. #7
    Join Date
    Mar 2012
    Posts
    16
    Thank you again.


    Quote Originally Posted by tonkuma View Post
    Yes You Can!

    But, it should be DETERMINISTIC and NO EXTERNAL ACTION.(Defaults are NOT DETERMINISTIC and EXTERNAL ACTION.)
    Please see my example of "CREATE FUNCTION GNR.F_FRACDAY".

    Here is more detailed descriptions.
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •