SEO

Hello,

I'm absolutly new to Informix. I have got an application which
accesses Informix (IBM IDS Version 9.40.UC3) via JDBC.
I need to limit the number of rows in query with an offset, for
implementing a simple paging mechanism.
In MySql it is something like that

select * from mytable limit 5, 5

and 5,5 means from offset 5 , 5 rows.
In Oracle you can do it with the MINUS operator for two queries:
select * from mytable WHERE ROWNUM <= (5+5) MINUS select * from
mytable WHERE ROWNUM <= 5

After researching I found FIRST and ROWID. The bad thing at FIRST is ,
that you cannot use it in sub-queries

SELECT FIRST 5 * FROM mytable WHERE EXISTS (SELECT FIRST 10 * FROM
mytable)

so that the query above is not working.
A combination of rowid and first does the job

SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
(SELECT * FROM mytable WHERE rowid < (257+10))

and first I was happy. But if you want add an ORDER BY Clause to that
statement, Informix gets an syntax error

SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
(SELECT * FROM mytable WHERE rowid < (257+10) ORDER BY mycol) ORDER BY
mycol

Any ideas ?
The other problem ist that the ROWID variable does not always start at
257, it depends on the table ...

Not sure if I misunderstand something here, but if you wnat elements 5
- 10, wy not select the first 10 and discard the first 5?

In order to select elements 5 - 10 the database would need to retrive
the all the first 10 anyway, no matter if the app or the database
passes the result back.

I can understand that you may want to reduce the network traffic by
not passing unneeded rows back to the app, and if thats the case you
could probably write a stored procedure or function to discard the
unwanted rows.




sepp__huber@web.de (sh) wrote in message news:<f0b9f72b.0401270635.51204954@posting.google. com>...
> Hello,
>
> I'm absolutly new to Informix. I have got an application which
> accesses Informix (IBM IDS Version 9.40.UC3) via JDBC.
> I need to limit the number of rows in query with an offset, for
> implementing a simple paging mechanism.
> In MySql it is something like that
>
> select * from mytable limit 5, 5
>
> and 5,5 means from offset 5 , 5 rows.
> In Oracle you can do it with the MINUS operator for two queries:
> select * from mytable WHERE ROWNUM <= (5+5) MINUS select * from
> mytable WHERE ROWNUM <= 5
>
> After researching I found FIRST and ROWID. The bad thing at FIRST is ,
> that you cannot use it in sub-queries
>
> SELECT FIRST 5 * FROM mytable WHERE EXISTS (SELECT FIRST 10 * FROM
> mytable)
>
> so that the query above is not working.
> A combination of rowid and first does the job
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10))
>
> and first I was happy. But if you want add an ORDER BY Clause to that
> statement, Informix gets an syntax error
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10) ORDER BY mycol) ORDER BY
> mycol
>
> Any ideas ?
> The other problem ist that the ROWID variable does not always start at
> 257, it depends on the table ...

It's entirely meaningless to attempt to use FIRST in this way because
you cannot guarantee the order in which the rows are stored or will be
returned. Similarly with rowid, which has the added complication that
you cannot assume the numbers will be consecutive.

Basically you need to be testing values of things, not some arbitrary
position in the physical storage.

You are making fundamental misunderstandings about how SQL, rather
than Informix, works.

Andy



sepp__huber@web.de (sh) wrote in message news:<f0b9f72b.0401270635.51204954@posting.google. com>...
> Hello,
>
> I'm absolutly new to Informix. I have got an application which
> accesses Informix (IBM IDS Version 9.40.UC3) via JDBC.
> I need to limit the number of rows in query with an offset, for
> implementing a simple paging mechanism.
> In MySql it is something like that
>
> select * from mytable limit 5, 5
>
> and 5,5 means from offset 5 , 5 rows.
> In Oracle you can do it with the MINUS operator for two queries:
> select * from mytable WHERE ROWNUM <= (5+5) MINUS select * from
> mytable WHERE ROWNUM <= 5
>
> After researching I found FIRST and ROWID. The bad thing at FIRST is ,
> that you cannot use it in sub-queries
>
> SELECT FIRST 5 * FROM mytable WHERE EXISTS (SELECT FIRST 10 * FROM
> mytable)
>
> so that the query above is not working.
> A combination of rowid and first does the job
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10))
>
> and first I was happy. But if you want add an ORDER BY Clause to that
> statement, Informix gets an syntax error
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10) ORDER BY mycol) ORDER BY
> mycol
>
> Any ideas ?
> The other problem ist that the ROWID variable does not always start at
> 257, it depends on the table ...

sepp__huber@web.de (sh) wrote in message news:<f0b9f72b.0401270635.51204954@posting.google. com>...
> Hello,
>
> I'm absolutly new to Informix. I have got an application which
> accesses Informix (IBM IDS Version 9.40.UC3) via JDBC.
> I need to limit the number of rows in query with an offset, for
> implementing a simple paging mechanism.
> In MySql it is something like that
>
> select * from mytable limit 5, 5
>
> and 5,5 means from offset 5 , 5 rows.
> In Oracle you can do it with the MINUS operator for two queries:
> select * from mytable WHERE ROWNUM <= (5+5) MINUS select * from
> mytable WHERE ROWNUM <= 5
>
> After researching I found FIRST and ROWID. The bad thing at FIRST is ,
> that you cannot use it in sub-queries
>
> SELECT FIRST 5 * FROM mytable WHERE EXISTS (SELECT FIRST 10 * FROM
> mytable)
>
> so that the query above is not working.
> A combination of rowid and first does the job
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10))
>
> and first I was happy. But if you want add an ORDER BY Clause to that
> statement, Informix gets an syntax error
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10) ORDER BY mycol) ORDER BY
> mycol
>
> Any ideas ?
> The other problem ist that the ROWID variable does not always start at
> 257, it depends on the table ...


I assume that you are using this to scroll through data for a user.
Showing them 5 records at a time in some order. If that is the case
then you can keep track of the max value of the sorting parameter.

For example

currPageBreak=0
currPageBreakKey=0
Select First 5 mt.*, xyz sort_column from mytable mt where
<some-condition or no condition for all of the records> and xyz >
currPageBreak and mytable_id > currPageBreakKey order by sort_column,
mytable_id

The application then processes each record and sets currPageBreak to
the last value of the sort_column and currPageBreakKey to the last
value of mytable_id. It then can re-execute the query with the above
values. I think this is a better way because it doesn't use special
non-standard SQL ( except of course the widely implemented first).

If you really want to be standard then I think that you can do it with
a five or six way nesting (its very icky).

select * from mytable mt1 where sort_value <= ( sql to calculate 5th
lowest record by sort_value. I will only type it for CASH of course
this assumes sort_value is unique which it usually isn't. In that case
you would use sql92 handy tuple comparison, which informix doesn't
implement but that you can simulate with string concatenation ).


By the way there may indeed be a syntax error in your sql. If mycol is
not in the select statement, which is required for your version of sql
then I think Informix may give you a syntax error.

Try this
SELECT FIRST 5 mycol, * FROM mytable WHERE rowid > (257-1+5) AND
EXISTS
(SELECT mycol, * FROM mytable WHERE rowid < (257+10) ORDER BY 1) ORDER
BY
1