rownum and between – oracle result sets

Limiting your resultset with rownum is a powerful feature in oracle sqlplus.  Because the rownum is applied to the result set, a between statement is not possible…  So here is a sneaky and cheeky way of doing a between.

insert into testdta.F55001CM
(select aian8, 109170, 141300, 'SQL', 'A', 'MOIRS'
  from ( select a.*, rownum rnum
           from (
  select aian8, 109170, 141300, 'SQL', 'A', 'MOIRS'
  from testdta.f03012,testdta.F0101
  where AIAN8 = ABAN8
  and substr(ABALKY, 1, 2) in ('AY','LM','NY','NA','EA')) a
          where rownum <= 236000)
where rnum >= 235000 )