[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • Last Modified:

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;
0
LeLeBrown
Asked:
LeLeBrown
  • 4
1 Solution
 
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
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LeLeBrownAuthor 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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now