[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

AS400 iSeries procedure getting SQL0802 error when try to run

Posted on 2012-12-31
6
Medium Priority
?
1,129 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

656 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