sqlguptateamdevelopersqlbasecentura

SQLBase union doesn't work with alias. Why?

I am trying to execute a SQL query using UNION in my SQLBase database. Also I want to use an alias for the column. But it doesn't work with the alias.

I've never used UNION but I always use alias.

It works:

SELECT color
FROM my_table
UNION ALL
SELECT color
FROM my_table;

It doesn't works:

SELECT color AS my_color
FROM my_table
UNION ALL
SELECT color AS my_color
FROM my_table;

The error is:

09814 PRS RCT Result column name can be only be specified with top select statement.

I have the SQLBase Language Reference (SQLBase 11.7) book but it doesn't have an answer.

Solution

  • as far as I know you can specify alias columnnames only in the first part of a union. This one should work:

    SELECT color AS my_color
FROM my_table
UNION ALL
SELECT color
FROM my_table;