ORA-00926: missing VALUES keyword

Question

I have moved the VALUES clause to different locations in the statement but no luck. I am a Novice and any assistance is appreciated. Thank you.

INSERT INTO KEYITEM.296 (KEYVALUECHAR, KEYSETNUM)
VALUES ('NOT PUBLIC', 0)
SELECT
    KEYITEM296.KEYVALUECHAR,
    KEYITEM296.KEYSETNUM
FROM
    ITEMDATA
LEFT OUTER JOIN
    KEYITEM296
ON
    (
       ITEMDATA.ITEMNUM = KEYITEM296.ITEMNUM)
WHERE
    ITEMDATA.ITEMTYPENUM = 862 ;

score 1 · Answer 1 · answered Aug 29 '12 at 20:49

1

I'm DB2 fan, but you cannot mix VALUES and SELECT in INSERT.

The correct SQL would be for INSERTing data:

INSERT INTO KEYITEM.296 (KEYVALUECHAR, KEYSETNUM)
SELECT
    KEYITEM296.KEYVALUECHAR,
    KEYITEM296.KEYSETNUM
FROM
    ITEMDATA
LEFT OUTER JOIN
    KEYITEM296
ON
    (
       ITEMDATA.ITEMNUM = KEYITEM296.ITEMNUM)
WHERE
    ITEMDATA.ITEMTYPENUM = 862

or

INSERT INTO KEYITEM.296 (KEYVALUECHAR, KEYSETNUM)
VALUES ('NOT PUBLIC', 0)

Or if you want to do INSERT and then SELECT:

INSERT INTO KEYITEM.296 (KEYVALUECHAR, KEYSETNUM)
VALUES ('NOT PUBLIC', 0);
SELECT
    KEYITEM296.KEYVALUECHAR,
    KEYITEM296.KEYSETNUM
FROM
    ITEMDATA
LEFT OUTER JOIN
    KEYITEM296
ON
    (
       ITEMDATA.ITEMNUM = KEYITEM296.ITEMNUM)
WHERE
    ITEMDATA.ITEMTYPENUM = 862 ;

answered Aug 29 '12 at 20:49

kovica

2,443
3
21
25

Kovica Tim execute two queries , on insert into – Aghilas Yakoub Aug 29 '12 at 20:50
and on left join, in the first query he insert datas, and in the second execute left join, but when he executes left join , he don't set ITEMNUM value – Aghilas Yakoub Aug 29 '12 at 20:51
One of the problems is KEYITEM.296 and KEYITEM296. Whichone is correct? – kovica Aug 29 '12 at 21:00
KEYITEM296 i think because table with name = 296 it's not normal, '.' it's used in order separate schema from table – Aghilas Yakoub Aug 29 '12 at 21:01
INSERT INTO KEYITEM296 (KEYVALUECHAR, KEYSETNUM) VALUES ('NOT PUBLIC', 0); Then in the select you want to see this inserted record? How if the ITENNUM is NULL. Is ITEMNUM a generated column? – kovica Aug 29 '12 at 21:03
But in left join condition he sets "KEYITEM296.ITEMNUM" so KEYITEM296 have this column, so i added in my query – Aghilas Yakoub Aug 29 '12 at 21:04
Right, this means that the comparison for the join is done on the ITENNUM record. Left join means give me all rows from the left table (ITEMDATA) and match them with the right table (KEYITEM296). If the match fails then you get a NULL, otherwise a data from the right table. For more see http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join – kovica Aug 29 '12 at 21:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/15989/discussion-between-kovica-and-candie) – kovica Aug 29 '12 at 21:14
So, if you don't get the row you inserted in the first SQL, this means that the ITEMNUM is NULL and you have to include it into the INSERT: INSERT INTO KEYITEM296 (KEYVALUECHAR, KEYSETNUM, ITEMNUM) VALUES ('NOT PUBLIC', 0, 'FOO'); – kovica Aug 29 '12 at 21:17

Aghilas Yakoub · Answer 2 · 2012-08-29T21:13:39.700

0

INSERT INTO KEYITEM296 (KEYVALUECHAR, KEYSETNUM, ITEMNUM)
VALUES ('NOT PUBLIC', 0, 862 );

  --You must complete your insert with ITEMNUM
  -- you must add ; separator
  -- you must delete '.' of your query

SELECT
    KEYITEM296.KEYVALUECHAR,
    KEYITEM296.KEYSETNUM
FROM
    ITEMDATA
LEFT OUTER JOIN
    KEYITEM296
ON
    (
       ITEMDATA.ITEMNUM = KEYITEM296.ITEMNUM)
WHERE
    ITEMDATA.ITEMTYPENUM = 862 ;

edited Aug 29 '12 at 21:13

answered Aug 29 '12 at 20:47

Aghilas Yakoub

28,516
5
46
51

ORA-00926: missing VALUES keyword

2 Answers2