UDTF - decimal data error

[itnutter]

I have created a function that calls my service program and all is well as long as my parameters are CHAR. When I add a packed decimal in the return parameters or integer as defined in my table, I get a decimal data error -

WHAT IS WRONG WITH THE DECIMAL PACKED FIELDS THAT THEY CANNOT BE HANDLED BY THE USER DEFINED FUNCTION???oR IS SOMETHING ELSE CAUSING THE PROBLEM. IF i TAKE OUT THE PACKED FIELDS ALL WORKS FINE.

PLEASE HELP?

Code:
Message . . . . : Decimal data error.

Cause . . . . . : The sign or the digit codes of the packed or the zoned
decimal operand is in error. Valid signs are hex A-F, valid digit range is
hex 0-9.


Code:
d GETHOM...
d pr
*INPUT PARAMETERS
d ACCNO 13P 0 Const
*OUTPUT PARAMETERS
d C3IDSEQ1O 10i 0
d C3BRNCDEO 6P 0
d C3INCDATEO 10A
d C3COLDATEO 10A
d C3PRO1O 1A
d C3PRO2O 1A
d C3NAMEO 80A
d C3IDREGNUO 20A
d C3POLREFO 30A
d C3EMPNOO 30A

*NULL INDICATORS
d n_acno 5i 0 const
d n_C3IDSEQ1O 5i 0 const
d n_C3BRNCDEO 5i 0 const
d n_C3INCDATEO 5i 0 const
d n_C3COLDATEO 5i 0 const
d n_C3PRO1O 5i 0 const
d n_C3PRO2O 5i 0 const
d n_C3NAMEO 5i 0 const
d n_C3IDREGNUO 5i 0 const
d n_C3POLREFO 5i 0 const
d n_C3EMPNOO 5i 0 const

*SQL PARAMETERS
d SQLSTT 5a
d Function 517a varying const
d Specific 128a varying const
d errorMsg 70a varying
d CallType 10i 0 const


p GETHOM...
p b export


*srv pgm
d GETHOM...
d pi
*INPUT PARAMETER
d ACNO 13P 0 const
*OUTPUT PARAMETERS
d C3IDSEQO 10i 0
d C3BRNCDEO 6P 0
d C3INCDATEO 10A
d C3COLDATEO 10A
d C3PRO1O 1A
d C3PRO2O 1A
d C3NAMEO 80A
d C3IDREGNUO 20A

d C3POLREFO 30A
d C3EMPNOO 30A

*NULL INDICATORS
d n_acno 5i 0 const
d n_C3IDSEQ1O 5i 0 const
d n_C3BRNCDEO 5i 0 const
d n_C3INCDATEO 5i 0 const
d n_C3COLDATEO 5i 0 const
d n_C3PRO1O 5i 0 const
d n_C3PRO2O 5i 0 const
d n_C3NAMEO 5i 0 const
d n_C3IDREGNUO 5i 0 const
d n_C3POLREFO 5i 0 const
d n_C3EMPNOO 5i 0 const


*SQL PARAMETERS
d SQLSTT 5a
d Function 517a varying const
d Specific 128a varying const
d errorMsg 70a varying
d CallType 10i 0 const

D c3 DS
d d3IDSEQ 10i 0
d d3BRNCDE 6P 0
d d3INCDATE d
d d3COLDATE d
d d3PRO1 1
d d3PRO2 1
d d3NAME 80
d d3IDREGNUM 20
d d3POLREF 30
d d3EMPNO 30


when CALLTYPE = CALL_FETCH;

EXEC SQL FETCH next FROM Cmscm03CSR INTO
:c3;
eval C3IDSEQO = d3IDSEQ ;
eval C3BRNCDEO = d3BRNCDE ;
eval C3INCDATEO =%CHAR(d3INCDATE) ;
eval C3COLDATEO =%CHAR(d3COLDATE) ;
eval C3PRO1O = d3PRO1 ;
eval C3PRO2O = d3PRO2 ;
eval C3NAMEO = d3NAME ;
eval C3IDREGNUO = d3IDREGNUM ;
eval C3POLREFO = d3POLREF ;
eval C3EMPNOO = d3EMPNO ;

* This is my table
C3IDSEQ1 B 9 0 1
C3ACCNO P 13 0 5
C3BRNCDE P 6 0 12
C3INCDATE L 10 16
C3COLDATE L 10 26
C3PRO1 A 1 36
C3PRO2 A 1 37
C3NAME A 80 38
C3IDREGNUM A 20 118
C3POLREF A 30 138
C3EMPNO A 30 168

My UD Table Function


Code:
CREATE FUNCTION CMSLIBD.GETHOM (
ACNO DECIMAL(13, 0) )
RETURNS TABLE (
C3IDSEQ1 INTEGER ,
C3BRNCDE DECIMAL (6, 0) ,
C3INCDATE CHAR(10) ,
C3COLDATE CHAR(10) ,
C3PRO1 CHAR(1) ,
C3PRO2 CHAR(1) ,
C3NAME CHAR(80) ,
C3IDREGNUM CHAR(20) ,
C3POLREF CHAR(30) ,
C3EMPNO CHAR(30) )
LANGUAGE RPGLE
SPECIFIC CMSLIBD.GETHOM
NOT DETERMINISTIC
MODIFIES SQL DATA
RETURNS NULL ON NULL INPUT
DISALLOW PARALLEL
EXTERNAL NAME 'CMSLIBD/CMSCM03SRV(GETHOM)'
PARAMETER STYLE DB2SQL ;

 

[Mercury2]

Check the ACNO (input parameter) value.

Philippe

 

[Mercury2]

Pls do NOT consider my first answer. There's no possibility to update or delete one's post <Grin>

Instead, tell us on what line the error happens as I can see no reason why the UDTF doesn't work.

Philippe