We help IT Professionals succeed at work.

Data mapping error on member QSQPTABL  - UDTF error

iskibinska
iskibinska asked
on
Hi , my UDTF sometimes gives me this error:

Data mapping error on member QSQPTABL.        
Data mapping error on member QSQPTABL.        
Data conversion or data mapping error.        
User-defined function error on member ORDERS.
Data mapping error on member QSQPTABL.        
Data mapping error on member QSQPTABL.        
Data mapping error on member QSQPTABL.        
Data conversion or data mapping error.        
User-defined function error on member ORDERS

When F1 on  'User-defined function error on member ORDERS' the error code is 1


  1 -- The external program or service program returned SQLSTATE 22003. The
text message returned from the program is: SQLP_L2.ORDVAL                
Comment
Watch Question

VP Technology / Senior Consultant
Commented:
Did you look up the SQL State 22003 error?  Just Google "iseries sql state 22003".  Here's the V5R3 InfoCenter topic, for example:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/ddp/rbal1sqlc.htm

Looks like numeric overflow (or divide by zero, maybe).  You did some math in your function and the result is too big (or too small if negative) for a field, which according to the message is ORDVAL.  You need to do some error handling in your function, test for a zero divisor before dividing, and/or alter your variables to be large enough to handle the largest and smallest possible result if you are performing math.

For example, if you have a QTY (5P0) and a PRICE (5P2) then an EXTENDED_PRICE would need to be at least 10P2.  If you are summing up a list of order lines, then you need to make sure your "TOTAL" variable is big enough to accommodate an order with the maximum number of lines your system allows at the maximum extended price your system allows.

- Gary Patterson

Author

Commented:
Thanks,


The problem was I was using the expession:
SET ORDVAL = ((PRODPRC*PRDQTY)-((PRODPRC*PRDQTY)*(DSCPRC/100)

when i broke it to smaller pieces
SET ORDVAL = TOTPRC - DISC*TOTPRC;

it started to work, strange

the definitions were as follows:

PRODPRC DECIMAL(7,2),
DSCPRC DECIMAL(5,2),
PRDQTY DECIMAL(7,0)
DECLARE  ORDVAL  DECIMAL(11,2);
DECLARE  WRKPP   DECIMAL(11,2);
DECLARE TOTPRC   DECIMAL(11,2);
DECLARE DISC DECIMAL(5,2);