VistaDB Support Center

Start a new topic

multiple select does not work

the following query does not work in vistadb where as the query right after that works fine. do i need to add anything to make it work or is it just not supported?

select count(*) / (select count(*) from table1) from table1 where id = 0

select count(*) / 2 from table 1 where id = 0

It looks like VistaDB is generally disallowing sub-queries in aggregate queries which is more aggressive than necessary--non-correlated sub-queries should be allowed.  We'll need to add logic to distinguish the safe sub-queries from the unsafe correlated sub-queries which can't be allowed in an aggregate query.

In the mean time, it does appear to accept building simple expressions around the Count(*) aggregate--such as the second query--so you can work around the error by saving the sub-query result to a scalar variable.  An equivalent query would be:

declare @count BigInt = (select count(*) from table1);

select count(*) / @count from table1 where id = 0

Offhand of course you shouldn't get inconsistent results, the best thing to do is open a support ticket to get something like this investigated.  I'll convert this over to a ticket so we can help you out.

Login to post a comment