Hi!

I am trying to do a nested query but Pervasive kept giving me syntax error. This is what I am trying to do:

select itemno, sum(qtysold) from
(select itemno, qtysold from icitem
union all
select itemno, qtysold from oeitem)

Note that the above is not the complete query.

I heard that Pervasive cannot handle complex queries. If so, how can I go about doing a select statement from a result set?

Thanks!

Create a temporary table, perform two inserts (one from icitem, one from oeitem). Then select from this table.

There are views, but they do not support the union keyword.

OR, if you have Sql Server you could run the grouping there. Assuming you have your client pervasive odbc dsn set up on the Sql Server box, run something like the following in Query Analyser:

select itemno, sum(qtysold) from openrowset('MSDASQL','DSN=whatever','select itemno, qtysold from icitem union all select itemno, qtysold from oeitem')
group by itemno