Advertisement
Guest User

Untitled

a guest
May 10th, 2019
114
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT * FROM (SELECT fnr || i1 || i2 || i3 || c1 || c2 pnr, CASE WHEN MOD(i3, 2) = 1 THEN 'F' ELSE 'M'END gender FROM ( WITH fnr AS (
  2.         SELECT '261081' fnr
  3.         , LPAD(CASE
  4.             WHEN SUBSTR('261081',5,6) >= 50
  5.             THEN TO_NUMBER(ROUND(dbms_random.VALUE(1,499)))
  6.             ELSE TO_NUMBER(ROUND(dbms_random.VALUE(500,999))) END, 2, '0') random_num
  7.         FROM dual)
  8.     SELECT  fnr, random_num,
  9.         TO_NUMBER(SUBSTR(fnr,1,1)) d1
  10.         , TO_NUMBER(SUBSTR(fnr,2,1)) d2
  11.         , TO_NUMBER(SUBSTR(fnr,3,1)) m1
  12.         ,  TO_NUMBER(SUBSTR(fnr,4,1)) m2
  13.         , TO_NUMBER(SUBSTR(fnr,5,1)) y1
  14.         , TO_NUMBER(SUBSTR(fnr,6,1)) y2
  15.         , TO_NUMBER(SUBSTR(random_num,0,1)) i1
  16.         , TO_NUMBER(SUBSTR(random_num,1,1)) i2
  17.         , TO_NUMBER(SUBSTR(random_num,2,1)) i3
  18.         , CASE WHEN MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) = 0 THEN 0
  19.                 ELSE 11-MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) END c1
  20.         , CASE WHEN MOD(((5*TO_NUMBER(SUBSTR(fnr,1,1))) + (4*TO_NUMBER(SUBSTR(fnr,2,1))) + (3*TO_NUMBER(SUBSTR(fnr,3,1))) + (2*TO_NUMBER(SUBSTR(fnr,4,1))) + (7*TO_NUMBER(SUBSTR(fnr,5,1))) + (6*TO_NUMBER(SUBSTR(fnr,6,1))) + (5*TO_NUMBER(SUBSTR(random_num,0,1))) +(4*TO_NUMBER(SUBSTR(random_num,1,1))) + (3*TO_NUMBER(SUBSTR(random_num,2,1))) + (2*(CASE WHEN MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) = 0 THEN 0
  21.                 ELSE 11-MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) END))), 11) = 0 THEN 0
  22.                 ELSE 11-MOD(((5*TO_NUMBER(SUBSTR(fnr,1,1))) + (4*TO_NUMBER(SUBSTR(fnr,2,1))) + (3*TO_NUMBER(SUBSTR(fnr,3,1))) + (2*TO_NUMBER(SUBSTR(fnr,4,1))) + (7*TO_NUMBER(SUBSTR(fnr,5,1))) + (6*TO_NUMBER(SUBSTR(fnr,6,1))) + (5*TO_NUMBER(SUBSTR(random_num,0,1))) +(4*TO_NUMBER(SUBSTR(random_num,1,1))) + (3*TO_NUMBER(SUBSTR(random_num,2,1))) + (2*(CASE WHEN MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) = 0 THEN 0
  23.                 ELSE 11-MOD(((3*TO_NUMBER(SUBSTR(fnr,1,1))) + (7*TO_NUMBER(SUBSTR(fnr,2,1))) + (6*TO_NUMBER(SUBSTR(fnr,3,1))) + TO_NUMBER(SUBSTR(fnr,4,1)) + (8*TO_NUMBER(SUBSTR(fnr,5,1))) + (9*TO_NUMBER(SUBSTR(fnr,6,1))) + (4*TO_NUMBER(SUBSTR(random_num,0,1))) + (5*TO_NUMBER(SUBSTR(random_num,1,1))) + (2*TO_NUMBER(SUBSTR(random_num,2,1)))), 11) END))), 11) END c2
  24.     FROM fnr)  WHERE c1 <10 AND c2<10);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement