Re: Selecting from sequence a specific number

replace 100 with x

with t2 as (select 1 d from dual union all select 1 from dual), t4 as (select t2.d from t2, t2 t2_),
t16 as (select t4.d from t4, t4 t4_),
t256 as (select t16.d from t16, t16 t16_), t65536 as (select t256.d from t256, t256 t256_) select yyy_seq.nextval from t65536 where rownum<=100

Ashraf Fouad wrote:
> Dears,
> I'm having small requirment with a sequence.
>
> Lets assume that I have sequence named "yyy_seq"
> If I executed "select yyy_seq.nextval from dual"
> ==> it will get me the value of next item in the sequence and advance
> the pointer of the sequence for the value after it.
>
> And
> if I executed "select yyy_seq.nextval from table_zzz"
> ==> it will get me result set of range of sequence starting from next
> value and ending with a value equals the start + # of rows in the
> table_zzz
> i.e.:
> Next value
> Next value + 1
> Next value + 2
> ...
> ...
> ...
> Next value + # of rows in table_zzz
>
> My need is:
> ------------
> I'm having a sequence, and I want to select range of values starting
> with the next value of this sequence and with length x, this specific
> value (x) is not known untill run time, and dynamic.
>
> I have other solutions but there are problems in each one:
> 1- Loop x times over "select yyy_seq.nextval from dual" ==> From
> performance wise is not acceptable, I may have 1000 items so it will
> be very slow.
>
> 2- Get the first item in the sequence and then alter the sequence to
> start with this value + x, as I don't have this privilege on DB.
>
> What I think should do the trick is to have the select statment return
> any dummy rows so generate the sequence as I wish, but it seems I
> can't figure it out!!!!!!!!!!
>
> Thanks
Received on Thu Nov 25 2004 - 09:51:14 CST