Retrieving multiple rows into a listview control from an ODBC source works well for simple SELECTs with a statement attribute of SQL_SCROLLABLE. How do I do this with a UNION query?

Answer 1

can you not define a view on the db server that does the union query for you, so from the client code it just looks like a single select?

if you can't, can you just issue the union operation as part of your select, e.g.

select some_fields from table1
union
select same_fields from table2

and treat the result as a single result set?

Answer 2

if the issue is just needing to get the last row to get the number of rows and caching the last few rows [i assume if there are a million items in the select that you're not populating a drop-list with all of them ;-)] then you may be able to take advantage of the ROW_NUMBER() function of sql server 2005

you could select count(*) from (select blah UNION select blah) to get the number of rows, then select ROW_NUMBER() as rownum,blah from (select blah UNION select blah) where rownum between minrow and maxrow to just fetch the rows that you need to display/cache

but seriously folks, if you're selecting items from a million-row table, you might want to consider a different mechanism

good luck!

Answer 3

have you tried making the union a derived table?

select * from (select field1, field from table1 union all slect field1, filed2 from table2) a