Results 1 to 10 of 10

Thread: Register UDF?

  1. #1
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248

    Unanswered: Register UDF?

    Hi, in my UDB 8.2 I created an UDF that represents the builtin 'CURRENT DATE' function. It has the name 'TODAY' and is nothing more than a SQL expression UDF. When run in the Development center it returns today's date. But when incorporated in a SQL statement (in the Control Center or my application) like this:
    Code:
    SELECT * FROM TABLENAME WHERE LASTDATE < TODAY;
    I get:
    SQL0206N "TODAY" is not valid in the context where it is used.
    SQLSTATE=42703
    It's executed by the same user as the owner of the UDF and according to the properties in the Control Center that user has EXECUTE privileges.
    What's missing here?

    Thanks in advance and greetings,
    Hans

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248

    Rephrase question

    Hi, regarding the question above I can say that the solution is to use the UDF as 'TODAY()'. Since it's meant as a translation of the Informix builtin SQL function 'TODAY' I need to use it without the parentheses. Is that possible?

    Tanks,
    Hans

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are using a UDF, then you have to have the parenthesis. Why not use CURRENT DATE?

    Andy

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, the application I'm working on is designed for usage with a Informix database and there TODAY is the builtin equivalent of DB2's CURRENT DATE. The database on my laptop I use for development is DB2 and I don't want to alter the applications code to fit the DB2 SQL syntax. A builtin function CURRENT (for representing a DATETIME value) would also be fit for my needs but I don't believe that's in DB2.
    Would a Java written stored procedure or a routine in C be able to do the trick by your knowledge?

    Thanks anyway,
    Hans

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are going to have to change the code. DB2 has no TODAY special register. You do not need to create anything in C or java.

    Andy

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    OK thanks. I think there might be another solution which I'm trying to find now.

    Regards,
    Hans

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My idea is to create UDF(for example, today_() ) on Informix and DB2.

  8. #8
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Thanks, but I thought of another similar function in both databases. For that I need to get the date of the first day of a given month in SQL. E.g. I need the date of the first day of today's month minus 5 months, what will be 2008-05-01. In Informix that's no problem for me but I'm still trying in DB2. Any suggestions in that matter are appreciated.

    Greetings,
    Hans

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example in DB2.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION Firstday
    (in_date  DATE
    ,in_month INTEGER
    )
     RETURNS DATE
     LANGUAGE SQL
     CONTAINS SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
    in_date - in_month MONTHS
    - (DAY(in_date - in_month MONTHS) - 1) DAYS
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    VALUES Firstday(DATE('2008-10-10'), 5);
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2008-05-01
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    VALUES Firstday(DATE('2008-10-31'), 1);
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2008-09-01
    
      1 record(s) selected.

  10. #10
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, the function as you wrote it is not deployeable in my development center. But the return expression works nicely in a single SQL statement so I got the picture. Thanks a lot!

    Greetings,
    Hans

Posting Permissions

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