Get rows between two values in an Dynamic SQL

, ,
1

Hi there,

I have a sql query wich returns about 527 entries. How can I limit it to get only the entries between two custom values?
Is this achievable in webMethods?
I tried using ROWNUM but it does not work.

Thank you and best regards!

2

Did you try using between ?

Thanks,

3

ROWNUM does not work with BETWEEN or < and > operations. It only works when used as “ROWNUM <= X”. A typical work around people use to overcome this is getting ROWNUM as a output column and then using BETWEEN or < or > operations.

Sample below:


SELECT *FROM
     (
          SELECT ROWNUM as RN, ST.* FROM (SELECT COL1, COL2, COL3 FROM SAMPLE_TABLE) ST
     ) 
WHERE RN >= 1 AND RN <= 10

= 1 AND RN <= 10

4

Prasad, I can able to apply rownum with between, Please see the attached snap. Please correct me if I am wrong.

Thanks,

RowNum_with_between.JPG
RowNum_with_between.JPG1079×545 35.2 KB

5

I was bit unclear when I explained. ROWNUM does not work with BETWEEN and < or > between all ranges. It has to always start with ‘1’.

Simple analogy I can give is, it works like TOP in Microsoft SQL Server. Can only give results sets from 1 to X but not actually records in mid ranges like X to Y

Hi MR, Please try something like “SELECT * FROM EMP WHERE ROWNUM BETWEEN 4 AND 10;”

Expected: Should return records between 4 and 10
Actual: No results

6

Prasad, I found the reason behind it, posting here as it is useful to all.

When the first row is brought from the database, it first assumes a rownum as 1, then it checks for the where clause, but as where clause fails in this condition ( rownum between 10 and 20) it neglects that row and fetches next row. but, this second row is still treated as rownum=1 , why because previous row was not successful.
so, every row is a first row… no row will get a rownum of 10 or above… resulting in ‘no rows selected’.

Thanks,