Solved

AS400 iSeries procedure getting SQL0802 error when try to run

Posted on 2012-12-31
6
1,086 Views
Last Modified: 2013-01-07
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;
0
Comment
Question by:LeLeBrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38732834
do you have decimal columns in the table you insert into?
it says something about decimal value error
0
 

Author Comment

by:LeLeBrown
ID: 38732932
No decimal columns in the table I am inserting into only character and one date field (DOB).
0
 

Author Comment

by:LeLeBrown
ID: 38732938
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Accepted Solution

by:
LeLeBrown earned 0 total points
ID: 38733237
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
 
LVL 27

Expert Comment

by:tliotta
ID: 38733907
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
 

Author Closing Comment

by:LeLeBrown
ID: 38750266
No expert suggestions were posted and I found the solution on my own.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question