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;
LeLeBrownAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LeLeBrownConnect With a Mentor Author Commented:
I found the problem. In my statement:
WHERE T2.NAMETYPE = 1  it should have been
WHERE T2.NAMETYPE = '001'. It is a character field.
I am still stumped as to why the query alone worked and the procedure got the error.
0
 
momi_sabagCommented:
do you have decimal columns in the table you insert into?
it says something about decimal value error
0
 
LeLeBrownAuthor Commented:
No decimal columns in the table I am inserting into only character and one date field (DOB).
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LeLeBrownAuthor Commented:
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.
0
 
tliottaCommented:
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
0
 
LeLeBrownAuthor Commented:
No expert suggestions were posted and I found the solution on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.