Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
SQLSTATE 42821 on an embedded INSERT
has no ratings.
Published: 10 Oct 2017
Revised: 10 Oct 2017 - 62 days ago
Last viewed on: 11 Dec 2017 (142 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQLSTATE 42821 on an embedded INSERT Published by: Chris Proctor on 10 Oct 2017 view comments(4)

Good morning. I am trying to write records to an empty copy of DB2 table based on journal entries read. I'm taking the RRN from the journal entry and performing an INSERT from the DB2 file into the empty copy. When I try it in iNav or STRPDM, it works fine, but in the program I get the SQLSTATE 42821. When I look at my job it says that a field is not compatible, but when I look at both files the field is identical (packed 3.0). I'm not sure what I'm missing. Here's what my INSERT logic looks like:

 sqlstmt = 'insert into invsskj select * from invssk where rrn(invssk) = ' + %char(joctrr) + ' with nc';

 exec sql execute immediate :sqlstmt;

Has anyone else encountered this issue. I've never tried to do this before, so maybe I'm approaching it from the wrong angle. Anyway, I'd appreciate any suggestions.

Thanks!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQLSTATE 42821 on an embedded INSERT
Posted: 2 months 3 days 6 hours 52 minutes ago
Edited: Tue, 10 Oct, 2017 at 13:59:41 (62 days ago)

Well, do you look at the content of SQLSTMT when the SQLSTATE = 42821?

Does JOCTRR contain a blank in that instance?

Also, I would avoid the "WITH NC" and instead just use:

EXEC SQL SET OPTION COMMIT = *NONE

Posted by: chrisp
Premium member *
Portland, OR
Comment on: SQLSTATE 42821 on an embedded INSERT
Posted: 2 months 3 days 6 hours 17 minutes ago

Hi Bob. I was thinking about adding the SET OPTION and removing the 'with nc'. Yes, I've stepped thru the program in debug and stopped at the SQLSTMT and it looks good. Here's what it looks like. In fact, I've copied it into STRSQL and it ran just fine. For some reason it's saying that the IDEPT fields are incompatible, but the files are identical. Here's the SQLSTMT value:

'insert into invsskj select * from invssk where rrn(invssk) = 214646561 with nc'   

A co-worker suggested that I just replace it with the following SQL command that does away with the reading of the INVSSKI file (journal entries) and does the INSERT for all the records at once. Which is a great idea, but it won't even compile! Lol. It's probably going to be something stupid and simple, but we're not seeing it! Here's how I changed the INSERT:

exec sql

insert into invsskj select invssk.* from invssk join invsski i on rrn(invssk) = i.joctrr;

The errors I'm seeing are:

MSG ID  SEV  RECORD  TEXT                                               
SQL0408  30      45  VALUE FOR COLUMN OR VARIABLE IDEPT NOT COMPATIBLE. 
SQL0117  30      45  STATEMENT CONTAINS WRONG NUMBER OF VALUES.         

I've checked both INVSSKJ and INVSSK and the files are identical. Very frustrating!

Thanks for the input!

Posted by: chrisp
Premium member *
Portland, OR
Comment on: SQLSTATE 42821 on an embedded INSERT
Posted: 2 months 3 days 5 hours 17 minutes ago

I figured it out, Bob. Don't understand why, but it's working. I had to remove the file from the F specs and specify the fields all within the the INSERT statement. When I took it out, it compiled and ran fine. Go figure!! Lol.

Thanks for the input!

Chris

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQLSTATE 42821 on an embedded INSERT
Posted: 2 months 3 days 4 hours 51 minutes ago

I wonder if you move the INSERT to its own SubProcedure, then call the subproc if it would have worked even with file on the F spec. Just make sure the host variable that contains the SQL statement is a local variable.