generating same positive and negative number in Oracle sequence

  • A+
Category:Languages

How can I generate the below numbers through sequence in oracle

2 -2 2 -2 2 -2 

And so on. I can generate 1,2,1,2,1,2 through cycle but above I am not able to use logic.

 


This seems like an odd requirement for a sequence, but if you really want to then you can do:

create sequence weird_seq start with 2 increment by 4 maxvalue 2 minvalue -2 cycle nocache;  select weird_seq.nextval from dual connect by level <= 10;     NEXTVAL ----------          2         -2          2         -2          2         -2          2         -2          2         -2 

The increment 4 is so it steps from -2 to +2. The maxval 2 is so the next call cycles round to the minval -2. And it needs to start with +/-2. The nocache is unfortunate but necessary, otherwise you get "ORA-04013: number to CACHE must be less than one cycle".

If you're just using these numbers as part of a query then you could do it with a hierarchical query instead:

select 2 * (2 * mod(level, 2) - 1) from dual connect by level <= 10;  2*(2*MOD(LEVEL,2)-1) --------------------                    2                   -2                    2                   -2                    2                   -2                    2                   -2                    2                   -2 

but then running multiple times will always start at +2, which might not be what you want. (Depends how it'll be used...)

You could potentially also generate the number on the fly from existing data using an analytic query, but again, it depends what you'll use this for.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: