what's wrong in the following SQL?

[fluorite]

Hi all,
I am developing a webboard using jsp & db2 and I am stuck in the following SQL statement.
----------------------------------------------------------
case 1)

select * from guestboard
where board_idx not in
( select board_idx from guestboard order by ref desc,re_step asc fetch first 10 rows only)
order by ref desc,re_step asc fetch first 10 rows only
----------------------------------------------------------

I don't know what's wrong with the SQL. I tried so many times and gave up. The funny thing is the following code which is modified a little bit from the original SQL statement works fine.
----------------------------------------------------------
case 2)

select * from guestboard
where board_idx not in
( select board_idx from guestboard )
order by ref desc,re_step asc fetch first 10 rows only
----------------------------------------------------------

In short, case 1 doens't work. Is there anybody who can solve this problem?

Any help would be greatly appreciated.

 

[Crox]

DB2 decides how to sort on subselects.

syntax of subselect:

4.2 subselect




________________________________________________________________________
| |
| |
| >>__select-clause__from-clause__ ______________ _____________________> |
| |_where-clause_| |
| |
| >__ _________________ __ _______________ ___________________________>< |
| |_group-by-clause_| |_having-clause_| |
| |
| |
|________________________________________________________________________|


regards,

Crox

 

[MarcLodge]

I can see what you're trying to do but can't think of a way in which you can do it using just raw SQL. I'd open a cursor, read thru the first 10, and then continue from there.

It might be worthwhile asking this in the ANSI SQL forum as there are some people there who dream in SQL (or so it seems).

By the way, I assume 2) returns no rows.

Marc

 

[blom0344]

You may get away with it using OLAP functions (If you have them available) to circumvent this.
This will only work for unique combinations of
ref desc,re_step, cause ranking gives equal counters for multiple combinations:

SELECT * FROM
(Select BOARDIDX AS A,REF_DESC AS B,RE_STEP AS C,
RANK() OVER (ORDER BY REF_DESC,RE_STEP) AS RANK#
from GUESTBOARD) XXX
WHERE XXX.RANK# > 10 T. Blom
Information analyst
tbl@shimano-eu.com