Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 (
- SELECT '261081' fnr
- , LPAD(CASE
- WHEN SUBSTR('261081',5,6) >= 50
- THEN TO_NUMBER(ROUND(dbms_random.VALUE(1,499)))
- ELSE TO_NUMBER(ROUND(dbms_random.VALUE(500,999))) END, 2, '0') random_num
- FROM dual)
- SELECT fnr, random_num,
- TO_NUMBER(SUBSTR(fnr,1,1)) d1
- , TO_NUMBER(SUBSTR(fnr,2,1)) d2
- , TO_NUMBER(SUBSTR(fnr,3,1)) m1
- , TO_NUMBER(SUBSTR(fnr,4,1)) m2
- , TO_NUMBER(SUBSTR(fnr,5,1)) y1
- , TO_NUMBER(SUBSTR(fnr,6,1)) y2
- , TO_NUMBER(SUBSTR(random_num,0,1)) i1
- , TO_NUMBER(SUBSTR(random_num,1,1)) i2
- , TO_NUMBER(SUBSTR(random_num,2,1)) i3
- , 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
- 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
- , 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
- 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
- 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
- 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
- FROM fnr) WHERE c1 <10 AND c2<10);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement