Insert failed with -206

[Emmaar]

Hello,

Can anyone tell why the below sql failed.

INSERT INTO
UP2DBAP.TDB01R09 ("TIMESTAMP", TS_TDB01R09, POL_NO, CASE_NO,
GRANTEE)
VALUES (
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
9558750,
0,
"JAMES O'REGGIO"
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -206, ERROR: JAMES O'REGGIO IS NOT A COLUMN OF AN IN
TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE,
NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION


The query works if I replace "JAMES O'REGGIO" with 'JAMES O''REGGIO'

Ramkumar

 

[leveyp]

You've answered your own question!
The double quotes cause the problem single quotes are fine.

 

[Dagon]

Single quote is of course a reserved character because it is used to delimit a string. DB2 would have way of knowing whether the "'" in "O'Reggio" is a character you want to include in the name or the end of the string.

To store strings with single quotes in them, you have to supply two successive single quotes ('', not &quot. This is converted by DB2 to one single quote e.g.

select '''Hello''' from sysibm.sysdummy1

gives

'Hello'