UNION clause in embedded SQL

Question

I'm using ingres 10S SQL, and I'm trying to write the following SQL statement in an embedded SQL C program. It works fine as a standalone SQL script, but compiling the eSQL program gets the error

%% Error in file localtask.sc, Line 498: E_EQ0244 Syntax error on 'union'.

INSERT INTO nr301_tab2 (authority_id)
SELECT a.authority_id
FROM nrremdets a, nrstatus_hierarchy z
WHERE a.authority_id = z.authority_id
UNION
SELECT a.authority_id
FROM nrsumsamts a, nrsumsdets b
WHERE a.authority_id = b.authority_id;

(line 498 is the UNION line) What's wrong with that UNION clause?

Answer 1

Just a slight tweak of the query and it should work. Try this:

INSERT INTO nr301_tab2 (authority_id)
SELECT authority_id
FROM
    (SELECT a.authority_id
     FROM nrremdets a, nrstatus_hierarchy z
     WHERE a.authority_id = z.authority_id
     UNION
     SELECT a.authority_id
     FROM nrsumsamts a, nrsumsdets b
     WHERE a.authority_id = b.authority_id) Result

The idea is to union the result sets, which will create a new result set to be inserted once in the table. By adding the parenthesis we make sure that this happens.