Re: create function that accepts and returns a date

[CRPence <CRPbottle@xxxxxxxxx>]

  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