Register UDF?

Tyveleyn · #1 (**permalink**) 10-09-08, 09:20

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:

Quote:

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

Tyveleyn · #2 (**permalink**) 10-09-08, 11:13

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

ARWinner · #3 (**permalink**) 10-09-08, 13:22

If you are using a UDF, then you have to have the parenthesis. Why not use CURRENT DATE?

Andy

Tyveleyn · #4 (**permalink**) 10-09-08, 13:47

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

ARWinner · #5 (**permalink**) 10-09-08, 14:11

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

Tyveleyn · #6 (**permalink**) 10-10-08, 00:41

OK thanks. I think there might be another solution which I'm trying to find now.

Regards,
Hans

tonkuma · #7 (**permalink**) 10-10-08, 01:32

My idea is to create UDF(for example, today_() ) on Informix and DB2.

Tyveleyn · #8 (**permalink**) 10-10-08, 02:19

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

tonkuma · #9 (**permalink**) 10-10-08, 02:58

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.

Tyveleyn · #10 (**permalink**) 10-10-08, 04:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech