Link to home
Start Free TrialLog in
Avatar of LeLeBrown
LeLeBrown

asked on

AS400 iSeries procedure getting SQL0802 error when try to run

I have written a procedure that is getting a error when I try to run it. The error is

A select or omit error occurred in record 0, record format *FIRST, member number 1 of file DB_NAMES in library AL_NAMES, because of condition 1 of the following conditions:
          1 - The data was not valid in a decimal field.
It contains a insert statement.

If I strip the insert statement out and run by itself, it works. I thought the error would be in the insert/select statement because that is all the procedure does basically. Does anyone know why a statement would work alone but not in a procedure?

Thanks


--  Generate SQL
--  Version:                         V5R4M0 060210
--  Generated on:                    12/28/12 15:28:27
--  Relational Database:             AL400
--  Standards Option:                DB2 UDB iSeries
 
SET PATH "QSYS","QSYS2","BBL" ;
DROP PROCEDURE AL_NAMES.SP_NAMES_TOPORDR;
CREATE PROCEDURE AL_NAMES.SP_NAMES_TOPORDR      

      LANGUAGE SQL       

      SPECIFIC AL_NAMES.SP_NAMES_TOPORDR       

      NOT DETERMINISTIC       

      MODIFIES SQL DATA       

      CALLED ON NULL INPUT       

      SET OPTION  ALWBLK = *ALLREAD ,       

      ALWCPYDTA = *OPTIMIZE ,       

      COMMIT = *NONE ,       

      DECRESULT = (31, 31, 00) ,       

      DFTRDBCOL = *NONE ,       

      DYNDFTCOL = *NO ,       

      DYNUSRPRF = *USER ,       

      SRTSEQ = *HEX         

      BEGIN




DELETE FROM AL_NAMES.NAMES_TOPORDER;

INSERT INTO AL_NAMES.NAMES_TOPORDER  (ORDRMBR, ORDRDESC, ORDRT, LASTNAME, FIRSTNAME, MIDDLENAME, DOB, NAMETYPE, SHIP)

SELECT T1.ORDRMBR, T1.ORDRDESC, T1.ORDRT, T2.LASTNAME, T2.FIRSTNAME, T2.MIDDLENAME, T2.DOB, T2.NAMETYPE, T1.SHIP
FROM AL_NAMES.DB_NAMES T2  JOIN AL_NAMES.DB_ORDER T1  ON T1.ORDRMBR = T2.ORDRMBR
WHERE T2.NAMETYPE = 1 AND T1.ORDRT IN ('002','003','005','007','021','022','026','033') AND T1.SHIP IN ('029','039','040','030','043')

ORDER BY T2.ORDRMBR ASC;









END;
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

do you have decimal columns in the table you insert into?
it says something about decimal value error
Avatar of LeLeBrown
LeLeBrown

ASKER

No decimal columns in the table I am inserting into only character and one date field (DOB).
I should also say that none of the columns in the select are numeric or decimal either. I originally had one column defined as numeric 3,0 in the output table. Thinking that may be the problem, I removed it. I then set up a totally different output table with only character and the one date field thinking that the old table definition (even though I was no longer using it) was causing some confusion.
ASKER CERTIFIED SOLUTION
Avatar of LeLeBrown
LeLeBrown

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am still stumped as to why...

Is this question 'Answered'? Or are you still looking for a reason that the standalone statement worked?

Post the actual statement that worked to see if we can analyze it. We'll need to see it along with the messages indicating success. A screenshot of a successful operation might be best.

Tom
No expert suggestions were posted and I found the solution on my own.