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?

You can't do a union when doing a insert query. – Ludo - Off the record Jun 10 '15 at 14:22 — Ludo - Off the record, Jun 10 '15 at 14:22

Radu Gheorghiu · Accepted Answer · 2015-06-11T08:03:53.640

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.

edited Jun 11 '15 at 08:03

answered Jun 10 '15 at 14:36

Radu Gheorghiu

20,049
16
72
107

What does `Result` signify in that snippet? I now get a syntax error on that, but it compiles fine if I remove it. – rojomoke Jun 10 '15 at 14:49
Ok, then remove it. I don't have experience with `Ingres` so I wasn't sure if it would be required or not, but most RDBMS systems require you to add a name for the result set generated by a `UNION`, if you want to reference the entire result set in the higher level of the query. – Radu Gheorghiu Jun 10 '15 at 14:50
Have you run it yet rojomoke? I was able to compile without a correlation name, such as result, but got a runtime error. With one it compiles and runs OK. I tested on latest 10S so might be worth checking your patch level. – PaulM Jun 10 '15 at 17:42
@PaulM So, it would be corect to leave the ‘Result‘ alias for the result set, at the end of the query? Like I mentioned before, I don't have experience with Ingres. – Radu Gheorghiu Jun 10 '15 at 18:04
For me it fails with a syntax error if a correlation name is supplied, but compiles fine without one. I'll let you know whether it runs tomorrow. – rojomoke Jun 10 '15 at 21:37
@RaduGheorghiu yes not only is it correct but according to the Ingres SQL Guide it should be required. – PaulM Jun 11 '15 at 08:03
@PaulM Ok, thanks for the update. I modified my answer since I also consider it to be correct. Maybe it depends on the IDE the OP is using? – Radu Gheorghiu Jun 11 '15 at 08:05
@RaduGheorghiu, @PaulM I finally got around to running this, and yes, with `Result` at the end, it fails with a syntax error. However, it turns out that RESULT is a reserved word. Replace it with 'myResult', and it's fine. – rojomoke Jun 17 '15 at 12:29

UNION clause in embedded SQL

1 Answers1