Hi,

I used a query with multiset
select
col1,col2,ilx.cnt -NVL((select count(*) from table1 x1
where x1.col1=x2.col1
and x1.col2=x2.col2
and x1.rowid > x2.rowid),0) l_nbr
from invoice@${srcinstance}:table2 x2
join table(multiset (select col1,col2,col3,count(*) as cnt
from table4 group by col1,col2,col3)) as ilx
on(ilx.col1=il.col1
and ilx.col1=il.col1
and ilx.col1=il.col1)
this same query,i was able to execute in informix version 9.4 with IUNLOAD OPTION
“INFORMIX-SQL Version 9.40.FC6W1”
Copyright IBM Corporation 1996, 2004


but the same query said "Not A Valid Select!!!" error when i executed the query in informix version 10.0
“INFORMIX-SQL Version 10.00.FC6WM2”
Copyright IBM Corporation 1996, 2006


why did this happen and what might be the equivalent query in 10.0 version
Can i use an inline query with iunload command and use multiset also in the 10.0 version.

execute in the dbaccess, it will show at exactly point the syntax error occur...

Thanks so much for the reply.
The problem is with the (-) operator.
In one of the informix version 9.4 the query is running fine with the iunload.
But the same query with iunload utility is not running fine with 10.0.

Running in dbaccess, what exactly the error message ?
What error number?

If possible, put the create table of the each table here, than we can try simulate the problem;

there is no specific error number it just said "not a valid select!!!" no error number. but i can note that its bcoz of a minus symbol (-)

When executed in dbaccess , any error return a message number .
ALL errors on Informix are classified with a number.
You can see this with command "finderr" .