Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Sql statement to invoke sql function

    Hi,

    I created a sql function to calculate the difference between two dates (age = age + 1 if month of birth >= 6 than age = age +1), in
    datef = today date
    datei = date of birth

    CREATE FUNCTION QGPL/DIFDATA ( DATEI CHAR , DATEF CHAR )
    RETURNS INT
    LANGUAGE SQL
    BEGIN
    DECLARE YEAR_AGE INTEGER DEFAULT 0;
    DECLARE MONTH_AGE INTEGER DEFAULT 0;
    DECLARE AGE INTEGER DEFAULT 0;
    SET YEAR_AGE =
    INT(substr ( digits(DATE(SUBSTR(CHAR( DATEF ), 1, 4)
    ||'-'||
    SUBSTR(CHAR( DATEF ), 5, 2)
    ||'-'||
    SUBSTR(CHAR( DATEF ), 7, 2))
    -
    DATE(SUBSTR(CHAR( DATEI ), 1, 4)
    ||'-'||
    SUBSTR(CHAR( DATEI ), 5, 2)
    ||'-'||
    SUBSTR(CHAR( DATEI ), 7, 2)) ) , 1, 4 ) ) ;
    SET MONTH_AGE =
    INT(substr ( digits(DATE(SUBSTR(CHAR( DATEF ), 1, 4)
    ||'-'||
    SUBSTR(CHAR( DATEF ), 5, 2)
    ||'-'||
    SUBSTR(CHAR( DATEF ), 7, 2))
    -
    DATE(SUBSTR(CHAR( DATEI ), 1, 4)
    ||'-'||
    SUBSTR(CHAR( DATEI ), 5, 2)
    ||'-'||
    SUBSTR(CHAR( DATEI ), 7, 2)) ) , 5, 2 ) ) ;
    SET AGE = ANOS ;
    CASE WHEN MONTH_AGE >= 6 THEN SET AGE = YEAR_AGE + 1; END CASE;
    RETURN YEAR_AGE ;
    END




    and in a sql statement line in Iseries (AS400), it created the function in QGPL with the name DIFDATA.

    Now i need to use the function in a sql statement to calculate the age and i'm doing this:
    SELECT NAME , QGPL/DIFDATA( 20110928 , 19000629) as YEAR_AGE FROM
    MYLIBL/TEST



    However i got this message:
    SQL State: 42703
    Code of seller: -206
    Message: [SQL0206] Column QGPL is not specified in the tables. Cause. . . . . : QGPL is not a table column * N * N. If the table is * N, QGPL is not a column of any table or view that can be referenced. Recovery. . : Do one of the following and try the request again: - Make sure that the column names and table are correctly specified in the instruction. - If this is a SELECT statement, ensure that all required tables were named in the FROM clause. - If the column was intended to be a correlated reference, qualify the column with the correct table designator.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is this QGPL/DIFDATA correct
    should it not be QGPL.DIFDATA
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Now i tried like you suggested but i gor another error:

    SQL State: 42704
    Code of seller: -204
    Message: [SQL0204] Difdata the QGPL type * N not found. Cause. . . . . : DateDiff in QGPL type * N not found. If the member name is * ALL, the table has no defined partitions. If this is an ALTER TABLE statement and the type is * N, a constraint was not found or partition. If this is not an ALTER TABLE statement and the type is * N not found an object from a function, procedure, or activator sequence. If it has not found a function DateDiff is the service program that contains the function. The function will not be found unless the external name and the name of use are exactly alike. Look in the job log a message that provides more details about the function name that is being sought and no name matching. Recovery. . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a group of us with the CL command CRTNODGRP. If not found an external function, make sure that the font (uppercase and lowercase) of EXTERNAL NAME on the CREATE FUNCTION statement is exactly equal to the font name exported by the service program.

    I our system, whe name the objects in library's with "/" and not "."

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You created the function with CHAR parameters.
    CREATE FUNCTION QGPL/DIFDATA ( DATEI CHAR , DATEF CHAR )
    ...
    But, you called it by INTEGER arguments.
    SELECT NAME , QGPL/DIFDATA( 20110928 , 19000629) as YEAR_AGE FROM
    MYLIBL/TEST
    Please reconsider your create function statement. What datatype of parameters do you want?


    By the way,
    Some unnecessary complexities(e.g. "CHAR( DATEF )")/doubtfull code(e.g. "RETURN YEAR_AGE ;") were included in your original function body.

Tags for this Thread

Posting Permissions

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