Re: SQL Error

I think an easier way to do this is to use my iDate functions and just
use the following equation.

SELECT iDate((Century * 1000000) + (Year * 10000) + (Month * 100) +
Day) From SYSIBM/SYSDUMMY1

As an alternative, you could simply add a function to XVIDAT_M01 that
calls function iDate_Default_Packed

Note this also answers your question of why it does not work. You must
have a null defined as a parameter for each field coming in..

Easiest way to handle these things is to put your service program in
debug and then see what comes in.

If you feel like you want to continue to use single fields vs. the
equation above, please let me know and I will extend my iDate
functions to handle it. Not much to it.

Create Function iDATE(Decimal(2,0), Decimal(2,0), Decimal(2,0),
Decimal(2,0))
Returns Date
Language RPGLE
External Name 'ILPGMR/XVIDAT(IDAT_SINGLEFIELDS)'
Deterministic
No SQL
Parameter Style SQL
Allow Parallel
No External Action;

* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* IDAT_SingleFields
* This function is receives an iSeries date as separate fields.
* Input - Century Number.
* Year Number
* Month Number
* In Data Null.
* Function Name being called.
* Specific Name.
* Output - Return Date.
* Return Date Null.
* SQL State on Exit.
* Diagnostic Message.
* Returns - None.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
p IDAT_SingleFields...
p b Export
d pi
d InCenturyNumber...
d 2p 0
d InYearNumber...
d 2p 0
d InMonthNumber...
d 2p 0
d InYearNumber...
d 2p 0
d OutDate...
d Like(StdDat)
d InCenturyNull...
d Like(StdIntSml)
d InYearNull...
d Like(StdIntSml)
d InMonthNull...
d Like(StdIntSml)
d InDayNull...
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

d DefaultFormat...
d s 10a Inz(cDefaultFormat)
d Varying
d FormatNull...
d s Like(StdIntSml)
d Inz(0)
d Packed80...
d s 8p 0
/Free

OutSQLState = cSQLStateOk;
Packed80 = %Dec((InCentury * 1000000) +
(InYear * 10000) +
(InMonth * 100) + InDay:
8:
0);

IDAT_Packed(Packed80 :
DefaultFormat :
OutDate :
InDataNull :
FormatNull :
OutNull :
OutSQLState :
InFunctionName :
InSpecificName :
OutDiagnosticMessage);

Return;

/End-Free
p e




On Mon, Jan 19, 2009 at 3:46 PM, Wintermute, Sharon
<Sharon.Wintermute@xxxxxxxxxxxxxxxx> wrote:

Ok SQL gurus,



What am I doing wrong?



I looked at Alan Campin's IDate routines as a basis for one I am trying
to write. Here is the SQL Statement I used to create the function.



create function iDate (numeric, numeric, numeric, numeric)

returns Date language RPGLE

external name 'MIQLIBP/MIQUTIL(IDATE)'

deterministic no SQL Parameter Style SQL Allow Parallel

No External Action

Function IDATE was created in QGPL.





Here is the prototype for the procedure within my service program.



P iDate B Export

D iDate PI

D pCC 2S 0

D pYY 2s 0

D pMM 2s 0

D pDD 2s 0

d pOutDate like(StdDate)

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



d ds

d wDate 8s 0

d wCC 2s 0 overlay(wDate: 1)

d wYY 2s 0 overlay(wDate: 3)

d wMM 2s 0 overlay(wDate: 5)

d wDD 2s 0 overlay(wDate: 7)



/FREE



Clear wDate;

wCC = pCC;

wYY = pYY;

wMM = pMM;

wDD = pDD;



test(de) wDate;

If %error;

OutNull = -1;

OutDiagnosticMessage = *Blank;

Else;

poutDate = %Date(wDate);

ENDIF;



OutSqlState = cSqlStateOk;





Now I can get it to work fine from RPGLE to RPGLE, but when I try to use
it within Sql it fails.



All I get is:



User-defined function error on member ARMAST.



I am simply trying to get started before finishing off the code.



TIA,





Sharon Wintermute



--
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.