Re:UDTF - Decimal data error

Suggest you should better debug your pgm to see your input field value is normal
Be sure your input field value is not blank. Try to defult *ZERO value to your packed field first.


------------------ Original ------------------
From: "Hill, Mark"<Mark.Hill@xxxxxxxxxxxx>;
Date: Sat, Apr 17, 2010 03:21 PM
To: "rpg400-l"<rpg400-l@xxxxxxxxxxxx>;

Subject: UDTF - Decimal data error


UDTF - Decimal data error on packed fields

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 ;



The information contained in this email is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful. No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this email is free of viruses. The views expressed in this email are, unless otherwise stated, those of the author and not those of HYPHEN Technology (Pty) Ltd or its management. HYPHEN Technology (Pty) Ltd reserves the right to monitor, intercept and block emails addressed to its users or take any other action in accordance with its email use policy.