审计分析中巧用“数据字典”

      数据字典是关于数据信息的集合，即对数据流图中包含的所有元素定义的集合。在结构化分析中，数据字典的作用是给数据流图上每个成分加以定义和说明。我们将数据字典的定义引入到审计分析中，用于查找数据错误或缺失。本文从车辆识别号分析和票据缺号查找案例来说明如何查找错误或缺失。

      一、车辆识别号校验分析

      车辆识别号（即VIN码）的第九位是校验码，根据其他16位计算而来，计算规则如下：VIN码中的每一位都是数字或大写字母，每个数字和大写字母对应一个数值；车辆识别号中除检验位之外的每一位都有一个权重；将每一位的数值和权重相乘后累加，累加数除以11所得余数即为检验码，如余数为10则检验码为X。在对车辆识别号进行查错分析时，我们可以巧用数据字典表来对VIN码进行校验。

      第一步：制作两张字典表，一张用来说明权重（如图1），一张用来说明转换值（如图2）。

      

      

      第二步：对VIN码进行提取、计算。

      由于VIN码位数比较多，我们可以利用游标循环来提取计算或者在excel中写出批量语句。

      方法一：利用游标提取SQL语句如下：

      declare a cursor for

      select VIN码，校验码=case sub-string（VIN码，9，1）when ‘X’ then 10else substring（VIN码，9，1）end

      from abaVIN码

      declare @vin-char（25），@jy char（6）

      open a

      fetch a into @vin，@jy

      print 'VIN码检验码余数'

      while

      @@FETCH_ST ATUS=0 begin

      declare @a int，@sum int

      select @a=1，@sum=0

      while @＜=17

      begin

      set @sum=@sum+（select对应值from取值字典表where原始值=SUBSTRING（@vin，@a，1）

      *（select权重from权重字典表where第X位左向右=@a）

      set@a=@a+l

      set@sum=@sum%11

      if@sum＜＞@jy

      print@vin+@jy+cast（@sum as varchar（2）

      fetch a into @vin，@jy

      close a

      deallocate a

      查找结果如图3

      

      方法二：在excel中生成批量语句

      Step 1：在excel中生成计算语句（如图4）

      

      Step 2：在SQL中进行校验

      select VIN码，检验码=case SUBSTRING（VIN码，9，1）when ‘x’ then 10 else SUBSTRING（VIN码，9，1）end，

      余数=cast（select对应值from取值字典表where原始值=SUBSTRING（VIN码，1，1））*（select权重from权重字典表where第X位左向右=1）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，2，1））*（select权重from权重字典表where第X位左向右=2）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，3，10）*（select权重from权重字典表where第X位左向右=3）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，4，1））*（select权重from权重字典表where第X位左向右=4）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，5，1））*（select权重from权重字典表where第X位左向右=5）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，6，1））*（select权重from权重字典表where第X位左向右=6）+

      （select对应值from取值字典表where原始值=SUBSTRING（VIN码，7，1））*（select权重from权重字典表where第X位左向右=7）+