Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered:

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    143
    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

  3. #3
    Join Date
    Aug 2005
    Posts
    140
    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...

  4. #4
    Join Date
    Jan 2006
    Posts
    38
    Quote Originally Posted by nitin_math
    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.
    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.
    Quote Originally Posted by stanislav.ondac
    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...
    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:
    Code:
    select FIRST 10 column1 from mytable where <some condition>
    UNION
    select column2 from mytable where <some other condition>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •