Why can't I use first in informix subqueries

Question

Why can't I use select first in subquery? Here I have some pseudo select which throws exception: Cannot use 'first', 'limit' or 'skip' in this context.

I don't understand why. I want to select first 10 IDs and then in other table rows which in this set

select * from Table1 where ID in ( select  first 10  ID from Table2)

How should I rewrite this select?

Answer 1

It is not very pretty but seems to work:

SELECT * FROM _tmp_table 
WHERE id IN 
   (SELECT id FROM 
      (SELECT FIRST 10 id FROM _tmp_table)
   )

Answer 2

Because it's design this way...
I believe the IBM Informix engineers have some technical limitation for that, because is a old feature request from lot of Informix users... (despite there is no RFE about this at IBM RFE site)

If you check the Informix Manuals you will found the answer quoted here:

The Projection clause cannot include the SKIP, FIRST, or LIMIT keywords in these contexts:

• when the SELECT statement is part of a view definition
• in a subquery, except in the FROM clause of the outer query
• in a cross-server distributed query in which a participating database server does not support the SKIP, FIRST, or LIMIT keywords.

Just like @Michael answer, just a clear example how works... This in version 11.70 .

$ dbaccess -e sysmaster x.sql

Database selected.


select first 10 tabname[1,20] from systables
;

tabname

systables
syscolumns
sysindices
systabauth
syscolauth
sysviews
sysusers
sysdepend
syssynonyms
syssyntable

10 row(s) retrieved.


select t.tabname[1,20] from (select first 2 * from systables) as  t


tabname

systables
syscolumns

2 row(s) retrieved.


Database closed.