检核SQL检核社会统一信用代码是否有效

规则参考：

SELECT 
per.关联人证件类型,
per.关联人证件号,
/*检核社会统一信用代码有效性*/
(case 
/*检核社会统一信用代码是否为18位*/
when length(per.关联人证件号) <>  '18' then '社会统一信用代码位数有误'
/*判断前1-2位是否符合规范*/
when substr(per.关联人证件号,1,2) not in ('11','12','13','19','21','31','41','51','52','53','59','61','71','81','91','92','93','99','A1','B1','C1','D1','E1','F1','G1','Y1') THEN '社会统一信用代码1-2位有误'
/*判断前3-8位是否符合规范*/
when translate(substr(per.关联人证件号,3,6),'0123456789','') <> '' then '社会统一信用代码3-8位有误'
/*判断前9-18位是否符合规范*/
when translate(substr(per.关联人证件号,9,10),'0123456789ABCDEFGHJKLMNPQRTUWXY','') <> '' then '社会统一信用代码9-17位有误'
/*第18位校验码验证*/
when 31 - mod((
substr(per.关联人证件号,1,1)*1
+substr(per.关联人证件号,2,1)*3
+substr(per.关联人证件号,3,1)*9
+substr(per.关联人证件号,4,1)*27
+substr(per.关联人证件号,5,1)*19
+substr(per.关联人证件号,6,1)*26
+substr(per.关联人证件号,7,1)*16
+substr(per.关联人证件号,8,1)*17
+(case when substr(per.关联人证件号,9,1) = 'A' then 10when substr(per.关联人证件号,9,1) = 'B' then 11when substr(per.关联人证件号,9,1) = 'C' then 12when substr(per.关联人证件号,9,1) = 'D' then 13when substr(per.关联人证件号,9,1) = 'E' then 14when substr(per.关联人证件号,9,1) = 'F' then 15when substr(per.关联人证件号,9,1) = 'G' then 16when substr(per.关联人证件号,9,1) = 'H' then 17when substr(per.关联人证件号,9,1) = 'J' then 18when substr(per.关联人证件号,9,1) = 'K' then 19when substr(per.关联人证件号,9,1) = 'L' then 20when substr(per.关联人证件号,9,1) = 'M' then 21when substr(per.关联人证件号,9,1) = 'N' then 22when substr(per.关联人证件号,9,1) = 'P' then 23when substr(per.关联人证件号,9,1) = 'Q' then 24when substr(per.关联人证件号,9,1) = 'R' then 25when substr(per.关联人证件号,9,1) = 'T' then 26when substr(per.关联人证件号,9,1) = 'U' then 27when substr(per.关联人证件号,9,1) = 'W' then 28when substr(per.关联人证件号,9,1) = 'X' then 29when substr(per.关联人证件号,9,1) = 'Y' then 30ELSE per.关联人证件号END
) * 20 
+(case when substr(per.关联人证件号,10,1) = 'A' then 10when substr(per.关联人证件号,10,1) = 'B' then 11when substr(per.关联人证件号,10,1) = 'C' then 12when substr(per.关联人证件号,10,1) = 'D' then 13when substr(per.关联人证件号,10,1) = 'E' then 14when substr(per.关联人证件号,10,1) = 'F' then 15when substr(per.关联人证件号,10,1) = 'G' then 16when substr(per.关联人证件号,10,1) = 'H' then 17when substr(per.关联人证件号,10,1) = 'J' then 18when substr(per.关联人证件号,10,1) = 'K' then 19when substr(per.关联人证件号,10,1) = 'L' then 20when substr(per.关联人证件号,10,1) = 'M' then 21when substr(per.关联人证件号,10,1) = 'N' then 22when substr(per.关联人证件号,10,1) = 'P' then 23when substr(per.关联人证件号,10,1) = 'Q' then 24when substr(per.关联人证件号,10,1) = 'R' then 25when substr(per.关联人证件号,10,1) = 'T' then 26when substr(per.关联人证件号,10,1) = 'U' then 27when substr(per.关联人证件号,10,1) = 'W' then 28when substr(per.关联人证件号,10,1) = 'X' then 29when substr(per.关联人证件号,10,1) = 'Y' then 30ELSE per.关联人证件号END
) * 29 
+(case when substr(per.关联人证件号,11,1) = 'A' then 10when substr(per.关联人证件号,11,1) = 'B' then 11when substr(per.关联人证件号,11,1) = 'C' then 12when substr(per.关联人证件号,11,1) = 'D' then 13when substr(per.关联人证件号,11,1) = 'E' then 14when substr(per.关联人证件号,11,1) = 'F' then 15when substr(per.关联人证件号,11,1) = 'G' then 16when substr(per.关联人证件号,11,1) = 'H' then 17when substr(per.关联人证件号,11,1) = 'J' then 18when substr(per.关联人证件号,11,1) = 'K' then 19when substr(per.关联人证件号,11,1) = 'L' then 20when substr(per.关联人证件号,11,1) = 'M' then 21when substr(per.关联人证件号,11,1) = 'N' then 22when substr(per.关联人证件号,11,1) = 'P' then 23when substr(per.关联人证件号,11,1) = 'Q' then 24when substr(per.关联人证件号,11,1) = 'R' then 25when substr(per.关联人证件号,11,1) = 'T' then 26when substr(per.关联人证件号,11,1) = 'U' then 27when substr(per.关联人证件号,11,1) = 'W' then 28when substr(per.关联人证件号,11,1) = 'X' then 29when substr(per.关联人证件号,11,1) = 'Y' then 30ELSE per.关联人证件号END
) * 25+(case when substr(per.关联人证件号,12,1) = 'A' then 10when substr(per.关联人证件号,12,1) = 'B' then 11when substr(per.关联人证件号,12,1) = 'C' then 12when substr(per.关联人证件号,12,1) = 'D' then 13when substr(per.关联人证件号,12,1) = 'E' then 14when substr(per.关联人证件号,12,1) = 'F' then 15when substr(per.关联人证件号,12,1) = 'G' then 16when substr(per.关联人证件号,12,1) = 'H' then 17when substr(per.关联人证件号,12,1) = 'J' then 18when substr(per.关联人证件号,12,1) = 'K' then 19when substr(per.关联人证件号,12,1) = 'L' then 20when substr(per.关联人证件号,12,1) = 'M' then 21when substr(per.关联人证件号,12,1) = 'N' then 22when substr(per.关联人证件号,12,1) = 'P' then 23when substr(per.关联人证件号,12,1) = 'Q' then 24when substr(per.关联人证件号,12,1) = 'R' then 25when substr(per.关联人证件号,12,1) = 'T' then 26when substr(per.关联人证件号,12,1) = 'U' then 27when substr(per.关联人证件号,12,1) = 'W' then 28when substr(per.关联人证件号,12,1) = 'X' then 29when substr(per.关联人证件号,12,1) = 'Y' then 30ELSE per.关联人证件号END
) * 13 +(case when substr(per.关联人证件号,13,1) = 'A' then 10when substr(per.关联人证件号,13,1) = 'B' then 11when substr(per.关联人证件号,13,1) = 'C' then 12when substr(per.关联人证件号,13,1) = 'D' then 13when substr(per.关联人证件号,13,1) = 'E' then 14when substr(per.关联人证件号,13,1) = 'F' then 15when substr(per.关联人证件号,13,1) = 'G' then 16when substr(per.关联人证件号,13,1) = 'H' then 17when substr(per.关联人证件号,13,1) = 'J' then 18when substr(per.关联人证件号,13,1) = 'K' then 19when substr(per.关联人证件号,13,1) = 'L' then 20when substr(per.关联人证件号,13,1) = 'M' then 21when substr(per.关联人证件号,13,1) = 'N' then 22when substr(per.关联人证件号,13,1) = 'P' then 23when substr(per.关联人证件号,13,1) = 'Q' then 24when substr(per.关联人证件号,13,1) = 'R' then 25when substr(per.关联人证件号,13,1) = 'T' then 26when substr(per.关联人证件号,13,1) = 'U' then 27when substr(per.关联人证件号,13,1) = 'W' then 28when substr(per.关联人证件号,13,1) = 'X' then 29when substr(per.关联人证件号,13,1) = 'Y' then 30ELSE per.关联人证件号END
) * 8 +(case when substr(per.关联人证件号,14,1) = 'A' then 10when substr(per.关联人证件号,14,1) = 'B' then 11when substr(per.关联人证件号,14,1) = 'C' then 12when substr(per.关联人证件号,14,1) = 'D' then 13when substr(per.关联人证件号,14,1) = 'E' then 14when substr(per.关联人证件号,14,1) = 'F' then 15when substr(per.关联人证件号,14,1) = 'G' then 16when substr(per.关联人证件号,14,1) = 'H' then 17when substr(per.关联人证件号,14,1) = 'J' then 18when substr(per.关联人证件号,14,1) = 'K' then 19when substr(per.关联人证件号,14,1) = 'L' then 20when substr(per.关联人证件号,14,1) = 'M' then 21when substr(per.关联人证件号,14,1) = 'N' then 22when substr(per.关联人证件号,14,1) = 'P' then 23when substr(per.关联人证件号,14,1) = 'Q' then 24when substr(per.关联人证件号,14,1) = 'R' then 25when substr(per.关联人证件号,14,1) = 'T' then 26when substr(per.关联人证件号,14,1) = 'U' then 27when substr(per.关联人证件号,14,1) = 'W' then 28when substr(per.关联人证件号,14,1) = 'X' then 29when substr(per.关联人证件号,14,1) = 'Y' then 30ELSE per.关联人证件号END
) * 24+(case when substr(per.关联人证件号,15,1) = 'A' then 10when substr(per.关联人证件号,15,1) = 'B' then 11when substr(per.关联人证件号,15,1) = 'C' then 12when substr(per.关联人证件号,15,1) = 'D' then 13when substr(per.关联人证件号,15,1) = 'E' then 14when substr(per.关联人证件号,15,1) = 'F' then 15when substr(per.关联人证件号,15,1) = 'G' then 16when substr(per.关联人证件号,15,1) = 'H' then 17when substr(per.关联人证件号,15,1) = 'J' then 18when substr(per.关联人证件号,15,1) = 'K' then 19when substr(per.关联人证件号,15,1) = 'L' then 20when substr(per.关联人证件号,15,1) = 'M' then 21when substr(per.关联人证件号,15,1) = 'N' then 22when substr(per.关联人证件号,15,1) = 'P' then 23when substr(per.关联人证件号,15,1) = 'Q' then 24when substr(per.关联人证件号,15,1) = 'R' then 25when substr(per.关联人证件号,15,1) = 'T' then 26when substr(per.关联人证件号,15,1) = 'U' then 27when substr(per.关联人证件号,15,1) = 'W' then 28when substr(per.关联人证件号,15,1) = 'X' then 29when substr(per.关联人证件号,15,1) = 'Y' then 30ELSE per.关联人证件号END
) * 10+(case when substr(per.关联人证件号,16,1) = 'A' then 10when substr(per.关联人证件号,16,1) = 'B' then 11when substr(per.关联人证件号,16,1) = 'C' then 12when substr(per.关联人证件号,16,1) = 'D' then 13when substr(per.关联人证件号,16,1) = 'E' then 14when substr(per.关联人证件号,16,1) = 'F' then 15when substr(per.关联人证件号,16,1) = 'G' then 16when substr(per.关联人证件号,16,1) = 'H' then 17when substr(per.关联人证件号,16,1) = 'J' then 18when substr(per.关联人证件号,16,1) = 'K' then 19when substr(per.关联人证件号,16,1) = 'L' then 20when substr(per.关联人证件号,16,1) = 'M' then 21when substr(per.关联人证件号,16,1) = 'N' then 22when substr(per.关联人证件号,16,1) = 'P' then 23when substr(per.关联人证件号,16,1) = 'Q' then 24when substr(per.关联人证件号,16,1) = 'R' then 25when substr(per.关联人证件号,16,1) = 'T' then 26when substr(per.关联人证件号,16,1) = 'U' then 27when substr(per.关联人证件号,16,1) = 'W' then 28when substr(per.关联人证件号,16,1) = 'X' then 29when substr(per.关联人证件号,16,1) = 'Y' then 30ELSE per.关联人证件号END
) * 30+(case when substr(per.关联人证件号,17,1) = 'A' then 10when substr(per.关联人证件号,17,1) = 'B' then 11when substr(per.关联人证件号,17,1) = 'C' then 12when substr(per.关联人证件号,17,1) = 'D' then 13when substr(per.关联人证件号,17,1) = 'E' then 14when substr(per.关联人证件号,17,1) = 'F' then 15when substr(per.关联人证件号,17,1) = 'G' then 16when substr(per.关联人证件号,17,1) = 'H' then 17when substr(per.关联人证件号,17,1) = 'J' then 18when substr(per.关联人证件号,17,1) = 'K' then 19when substr(per.关联人证件号,17,1) = 'L' then 20when substr(per.关联人证件号,17,1) = 'M' then 21when substr(per.关联人证件号,17,1) = 'N' then 22when substr(per.关联人证件号,17,1) = 'P' then 23when substr(per.关联人证件号,17,1) = 'Q' then 24when substr(per.关联人证件号,17,1) = 'R' then 25when substr(per.关联人证件号,17,1) = 'T' then 26when substr(per.关联人证件号,17,1) = 'U' then 27when substr(per.关联人证件号,17,1) = 'W' then 28when substr(per.关联人证件号,17,1) = 'X' then 29when substr(per.关联人证件号,17,1) = 'Y' then 30ELSE per.关联人证件号END
) * 28
),31
)  <>  (case when substr(per.关联人证件号,18,1) = 'A' then 10when substr(per.关联人证件号,18,1) = 'B' then 11when substr(per.关联人证件号,18,1) = 'C' then 12when substr(per.关联人证件号,18,1) = 'D' then 13when substr(per.关联人证件号,18,1) = 'E' then 14when substr(per.关联人证件号,18,1) = 'F' then 15when substr(per.关联人证件号,18,1) = 'G' then 16when substr(per.关联人证件号,18,1) = 'H' then 17when substr(per.关联人证件号,18,1) = 'J' then 18when substr(per.关联人证件号,18,1) = 'K' then 19when substr(per.关联人证件号,18,1) = 'L' then 20when substr(per.关联人证件号,18,1) = 'M' then 21when substr(per.关联人证件号,18,1) = 'N' then 22when substr(per.关联人证件号,18,1) = 'P' then 23when substr(per.关联人证件号,18,1) = 'Q' then 24when substr(per.关联人证件号,18,1) = 'R' then 25when substr(per.关联人证件号,18,1) = 'T' then 26when substr(per.关联人证件号,18,1) = 'U' then 27when substr(per.关联人证件号,18,1) = 'W' then 28when substr(per.关联人证件号,18,1) = 'X' then 29when substr(per.关联人证件号,18,1) = 'Y' then 30ELSE substr(per.关联人证件号,18,1)END
) then '校验码有误' else '校验通过' end) as 社会统一信用代码校验
FROM 2023年数据质量考核客户基础信息_个人关联方明细数据 per

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！