Question
Lewis Houlden · Jun 20

INSERT Statement not working

Hi all

I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).

Insert statement:

INSERT INTO Phu_Replay_Schema.ReplayMessageModel (Completed, MessageHeaderId, NewHeaderId, NewTargetName) VALUES (true, 3616, null, 'Router_ReplayHL7')

I get a SQLCODE -12 error, and I can't understand why: A term expected, beginning with one of the following: identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, %STRING, %TRUNCATE, or %UPPER>]

I'm pretty sure I'm missing something blatantly obvious, but I just cant see it! I've tried a load of variations but to no avail.

Thank you!

Product version: IRIS 2021.2
0
0 78
Discussion (5)1
Log in or sign up to continue

Hey Lewis.

Could you try swapping out "true" for it's bool equivalent? So try:

INSERT INTO Phu_Replay_Schema.ReplayMessageModel (Completed, MessageHeaderId, NewHeaderId, NewTargetName) VALUES (1, 3616, null, 'Router_ReplayHL7')

Hi Julian - thanks for replying. I have tried that, and it changes the error to -400 which is a generic fatal error. 

That just means you're one step closer to solving the issue!

What are the data types for each field you're attempting to insert into?

Have you tried just inserting a row with just 1 of the fields populated? Something like:

INSERT INTO Phu_Replay_Schema.ReplayMessageModel (Completed) VALUES (1)

Julian, thank you! In leading to the -400 error, I opened the class, compiled it, and re-ran the insert and it worked! Thanks again., 

Does  NewHeaderId allow NULL or is it required ?
in addition, you could try '' instead of NULL