On Wed, Jun 27, 2012 at 12:22 PM, roydamman <damman@...> wrote:

>
> 2 records:
> insert into "TEST" ("TESTID", "NAME") values(1, 'EEN')
> insert into "TEST" ("TESTID", "NAME") values(2, 'twee')
>
> First query:
> "select "TESTID", "NAME" from test order by upper(2)"
> Answer (As expected):
> 1, EEN
> 2, twee
>
> Second query:
> "select "TESTID", "NAME" from test
> union select "TESTID", "NAME" from test order by 2"
> Answer (Unexpected):
> 1, EEN
> 2, twee
> The answer has only two rows. I would expect each row double, in total 4
> rows. Is this normal behaviour?
>

Yes. By default a UNION includes a projection which eliminates duplicates.
To get what you want, try
UNION ALL.

>
> Third query:
> "select "TESTID", "NAME" from test
> union select "TESTID", "NAME" from test order by upper(2)"
> Answer (Unexpected):
> An error message: ISC ERROR CODE:335544569
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -104
> Invalid command
> invalid ORDER BY clause
>
> Especially the third query is annoying me. Is this according to the
> standard or is it a bug? Why is does "order by upper(2)" not work with an
> union and work without an union?

Because in earlier versions of the SQL standard, a query expression (which
is the technical name for the select in a union) cannot include an order by
clause. The 2008 standard does allows order by clauses in query
expressions.

Good luck,

Ann


[Non-text portions of this message have been removed]