How can the post be empty? The text seems to have disappeared.. and I was thinking that I had not got any replies..

Okay.. so here's the thing:

I want to use a query on informix like this:

select FIRST 10 column1 from mytable where <some condition>
UNION
select FIRST 10 column2 from mytable where <some other condition>

Is this a valid query? I guess the FIRST in the first select would ask for the 10 from the union.. but even that doesn't seem to work. Do I have any alternatives to achieve something like this?

Thanks for having a look. Best regards.

Hi,

Insert the result of 1st query into a temp table and run the second query and insert again in the same temp table and then run select * from the temp table.

Bye

Nitin

Hi, query seems OK, but I dont know if you want to select all values from both tables or only distinct values. If you want all values you must use
union all:
select FIRST 10 column1 from mytable where <some condition>
UNION ALL
select FIRST 10 column2 from mytable where <some other condition>

I dont see the reason why it shouldnt work...

Nitin, I did something like this... I knew this was going to create a problem.. basically, right now - I make two different queries to the db and return the sum of both the resultsets got (the selects are far too complex actually than what I have shown but that's what actually represents my concern). I wanted to hit the db just once.. but I guess this is going to be okay for now. I know this is not an elegant solution. May be I should write a stored proc that does what you actually have suggested. Thanks for the response.
Firstly, there is just one table involved. I know UNION ALL, UNION is not an issue. The issue was that the FIRST clause was not allowed in the two selects with a UNION. I guess this is allowed:
Here I used FIRST just once and in the first select. This probably means that after doing the UNION, select the first 10 from the UNIONed resultset. They did not allow to do two select with FIRST and UNION them. The query analyzer or interpreter (or whatever may be it called) would otherwise not be able to resolve the ambiguity. (By the way, I was using eSQL)

For now, temporarily, solved my problem. Thanks for your responses. Would love to get a way to do this in just one hit to the db though (without using the stored procedure - i know that's a solution and may be that's what I am going to implement later). Thanks again guys. Best regards.