Generator usage in insert statement of Firebird

Question

I have the following statement executed in Flamerobin (Firebird 2.1). It keeps reporting the following error on editor window

" Dynamic SQL Error SQL error code = -104 Invalid command count of column list and variable list do not match "

Normally columns are matching and there is no any mismatching in column counts, but having the generator, which I believe works in singleton basis, and resulting dataset from union statement can be the source of the error.

insert into test_table (load_name, X, Y, Z) values (
gen_id(gen_new,1), (select X_I,Y_I,Z_I from 
(
    select e1.X_I,e1.Y_I,e1.Z_I from ELEMENTS e1
    union 
    select e2.X_J,e2.Y_J,e2.Z_J from elements e2
) 
) )

DDL of table is:

CREATE TABLE TEST_TABLE
(
  LOAD_NAME Char(20) NOT NULL,
  FACTOR Integer,
  NEW1 Integer,
  "new" Float,
  X Float,
  Y Float,
  Z Float,
  PRIMARY KEY (LOAD_NAME)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TEST_TABLE TO  SYSDBA WITH GRANT OPTION;

Kamil Gosciminski Kamil Gosciminski · Accepted Answer · 2016-01-04T21:45:42

From what I know your INSERT syntax isn't supported by Firebird. You can either provide column values using VALUES syntax or retrieve them using SELECT statement, but I doubt they can be combined within 1 statement.

You probably already know that Firebirds' documentation isn't full of fruits, but can be of limited use.

Below should be working

insert into test_table (load_name, X, Y, Z) 
select gen_id(gen_new, 1), foo.X, foo.Y, foo.Z
from (
  select e1.X_I AS X, e1.Y_I AS Y, e1.Z_I AS Z from elements e1
  union
  select e2.X_J, e2.Y_J, e2.Z_J from elements e2
  ) foo

Note: - Aliasing columns from the first select statement in union feeding fashion would make them available in such names in the outer query.

Generator usage in insert statement of Firebird

2 Answers