Re: create function that accepts and returns a date

The SQL considers the literal [aka constant] string value of '*mtdf' to be a VARCHAR, not a CHAR. Thus for lack of a function of the name SQ_GETDATE with the first argument of type DATE and the second argument of type VARCHAR, the expression sq_getdate(dateplaced,'*mtdf') can not find a routine to process that apparent function invocation.

One resolution, one that I prefer: Use VARCHAR(6) instead of CHAR for the SQL, and change the parameter declaration in the external procedure to a /varying/ declaration instead of fixed-length character to match the SQL change for the second argument.

Regards, Chuck

On 21 Jan 2013 11:28, tim.dclinc@xxxxxxxxx wrote:

Im trying to create a function that will accept a date field and a
constant and return a date field. So
sq_getdate(dateplaced,'*mtdf')

When i try to execute my sql, i get a "note found" message. I believe it
has to do with parameter types, but not sure how to fix it.

Here is my create command:
CREATE FUNCTION MYLIB/SQ_GETDATE( date, CHARACTER(6) )
RETURNS date
SPECIFIC SQ_GETDATE DETERMINISTIC LANGUAGE RPGLE NO SQL
NO EXTERNAL ACTION RETURNS NULL ON NULL INPUT SCRATCHPAD
NO FINAL CALL ALLOW PARALLEL
EXTERNAL NAME 'MYLIB/SQLUDF02(SQ_GETDATE)'
PARAMETER STYLE DB2SQL

Prototype:
d SQ_GETDATE...
d Pr
d datein D
d datefmt 6
d dateout D
D NullIndIn 4B 0
D NullIndOut 4B 0
D SQLState 5A
D FunctionName 517A CONST OPTIONS(*VARSIZE) VARYING
D SpecificName 128A CONST OPTIONS(*VARSIZE) VARYING
D DiagMessage 70A OPTIONS(*VARSIZE) VARYING
D ScratchPad 104A OPTIONS(*VARSIZE) VARYING
D CallType 4B 0
D DBInfo 1024A OPTIONS(*VARSIZE:*NOPASS)
D CONST VARYING