星空数据细节——科目全名数据库更新

您是否需要批量修改科目名称却苦于科目全名需要同步修改？这里提供科目全名重新拼写的SQL

步骤一：先检查一波科目的父级内码是否正确FPARENTID

 select a.FPARENTID, b.FACCTID,a.facctid,a.fnumber,b.fnumber, a.FACCTTBLID

 from  T_BD_ACCOUNT a join T_BD_ACCOUNT b  on a.FACCTTBLID=b.FACCTTBLID and SUBSTRING(a.FNUMBER,1,LEN(b.fnumber))=b.FNUMBER

 and SUBSTRING(a.FNUMBER,LEN(b.fnumber)+1,1)='.' and a.FLEVEL=b.FLEVEL+1

 join T_BD_ACCOUNTTABLE c on a.FACCTTBLID=c.FACCTTABLEID and c.FISENABLEDIST=0

 where   a.FPARENTID<>b.FACCTID 

 select a.FPARENTID, b.FACCTID,a.facctid,a.fnumber,b.fnumber, a.FACCTTBLID

 from  T_BD_ACCOUNT a join T_BD_ACCOUNT b  on a.FACCTTBLID=b.FACCTTBLID and SUBSTRING(a.FNUMBER,1,LEN(b.fnumber))=b.FNUMBER

 and SUBSTRING(a.FNUMBER,LEN(b.fnumber)+1,1)='.' and a.FLEVEL=b.FLEVEL+1 and a.FUSEORGID=b.FUSEORGID

 join T_BD_ACCOUNTTABLE c on a.FACCTTBLID=c.FACCTTABLEID and c.FISENABLEDIST=1

 where a.FPARENTID<>b.FACCTID

如发现错误可参考下面链接修复：

科目父级内码错误修复

步骤二：创建表获取获取当前科目情况

if exists (select 1 from sys.tables where name like 'chlaccountname')

drop table chlaccountname

select a.FACCTTBLID,FISENABLEDIST,a.FACCTID,FPARENTID,FLEVEL,a.FNUMBER,b.FNAME,b.FFULLNAME,convert(varchar(1000),'') FnewFULLNAME 

into chlaccountname

from T_BD_ACCOUNT a join T_BD_ACCOUNT_L b on a.FACCTID=b.FACCTID and b.FLOCALEID=2052 

join T_BD_ACCOUNTTABLE c on a.FACCTTBLID=c.FACCTTABLEID 

步骤三：重新拼写科目全名

with chl_fullname(FACCTID,fname) as 

(

select  FACCTID, convert(varchar(max),FNAME) from chlaccountname where FPARENTID=0

union all 

select b.FACCTID,convert(varchar(max),CONCAT(a.fname,'_',b.FNAME))  from chl_fullname a join chlaccountname b on b.FPARENTID=a.FACCTID

) 

update b set b.FnewFULLNAME=a.fname from  chl_fullname  a join chlaccountname b on a.FACCTID=b.FACCTID

步骤四：找到科目全名需要修改的数据，剩下的自行更新T_BD_ACCOUNT_L即可

select *  FROM chlaccountname WHERE FnewFULLNAME<>FFULLNAME