Re: create function that excepts and returns a date

[Alan Campin <alan0307d@xxxxxxxxx>]

First thing that I notice is that you don't have a null indicator defined
for the second parameter. I use SQL rather than DB2SQL and this is what my
call looks like to the packed value.

p IDAT_Packed_Default...
p                 b                   Export
d                 pi
d   InPackedValue...
d                                8p 0
d   OutDate...
d                                     Like(StdDat)
d   InDataNull...
d                                     Like(StdIntSml)
d   OutNull...
d                                     Like(StdIntSml)
d   OutSQLState...
d                                     Like(StdSQLState)
d   InFunctionName...
d                              139a   Varying
d   InSpecificName...
d                              128a   Varying
d   OutDiagnosticMessage...
d                               70a   Varying

Also, what value would you have passing a date and returning a date. Date
data types don't have formats. You can't pass a MDY and get back a CYMD.
The date is stored in an internal date format. When you display it, it
displays according to the method you have selected.



On Mon, Jan 21, 2013 at 1:28 PM, tim.dclinc@xxxxxxxxx
<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
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.