Numeric Series (5) Query
-- To get series of numbers incremented by five
-- 1,6,11,16,21,....
-- 2,7,12,17,22,....
-- 3,8,13,18,23,.... etc.
-- By changing the MOD(value,x) and some customization, other number series can be 
-- computed.

WITH q_tab AS
(SELECT  level ref_val,  MOD(level,5)  mod_val  FROM DUAL
 CONNECT BY level <= 30),
q_tab2 AS
(SELECT ref_val, mod_val,
  DECODE(mod_val,1, ref_val, NULL) val_1,
  DECODE(mod_val,2, ref_val, NULL) val_2,
  DECODE(mod_val,3, ref_val, NULL) val_3,
  DECODE(mod_val,4, ref_val, NULL) val_4,
  DECODE(mod_val,0, ref_val, NULL) val_5
 FROM q_tab)
SELECT * FROM q_tab2;

Numeric Series, 1, 6, 11, ...


-- Filter to get only needed series
-- 1,6,11,16,21,... etc.

WITH q_tab AS
(SELECT  level ref_val,  MOD(level,5)  mod_val
 FROM DUAL
 CONNECT BY level <= 30),
q_tab2 AS
(SELECT ref_val, mod_val,
  DECODE(mod_val,1, ref_val, NULL) val_1,
  DECODE(mod_val,2, ref_val, NULL) val_2,
  DECODE(mod_val,3, ref_val, NULL) val_3,
  DECODE(mod_val,4, ref_val, NULL) val_4,
  DECODE(mod_val,0, ref_val, NULL) val_5
 FROM q_tab)
SELECT ref_val, mod_val
FROM q_tab2
WHERE  mod_val = &i_val;

=> Numeric Series Query Output
HEXTORAW/RAWTOHEX Query
SELECT level, chr(level),
 length(trim(chr(level))) length_,
 hextoraw(level) hex_2_raw,
 rawtohex(level) raw_2_hex,
 TRIM(TO_CHAR(level,'xxxx')) no_1_hex
FROM  DUAL
CONNECT BY  level <=256;

Oracle and Java are registered trademarks of Oracle and/or its affiliates

Last Revised On: July 11th, 2015

  278