文档库

最新最全的文档下载
当前位置:文档库 > 一些常用sql脚本整理

一些常用sql脚本整理

一些常用脚本

nc产品支持王静

一些实施和维护人员经常遇到的问题,在这稍做整理,希望对大家有所帮助。

注:执行这些脚本前,一定要备份数据。在测试环境中测试没有问题后,方可使用。

1.预算中将系统预制用户tbadmin删除,如何恢复?

首先,建立前台建立用户tbadmin,后执行以下脚本

update sm_user set cuserid= 'TB_NEW100000000004OP' where user_code='tbadmin'

commit

2.5x版本固定资产做变动或者减少的时候,经常参照不到,如何处理?

问题原因:已经对该卡片做过变更操作,但是没有确认完成,就有可能造成单据丢失

解决sql:

通过公司编码查出公司pk

select pk_corp from bd_corp where unitcode='3103' 1082

通过卡片编码和公司pk.查出卡片pk和变动单pk

select pk_card, bill_code from fa_card where card_code='0000000001' and pk_corp='1082'

查出bill_code 为1082V510000000000HR0

通过变动单pk,查询变动单号

select bill_code from fa_log where code='1082V510000000000HR0'

查出bill_code 为0_HG_BD0806180001

根据以上查询出结果,进行修改,删除变动单垃圾数据,删除前要查询唯一性,及备份相应的表

select * from fa_altersheet where altersheet_code='HG_BD0806180001' and pk_corp='1082' update fa_altersheet set dr=1 where altersheet_code='HG_BD0806180001' and pk_corp='1082';

select * from fa_bill where billcode='HG_BD0806180001' and pk_corp='1082'

update fa_bill set dr=1 where billcode='HG_BD0806180001' and pk_corp='1082';

select * from fa_card where bill_code='0_HG_BD0806180001'

update fa_card set bill_code='' where bill_code='0_HG_BD0806180001';

select * from fa_log where bill_code='0_HG_BD0806180001'

update fa_log set bill_code='' where bill_code='0_HG_BD0806180001'

3.删除非现金科目的现金流量辅助信息

执行以下sql,注意替换相应的账簿pk

create table ufbf2009071501 as select * from gl_cashflowcase where pk_glorgbook = '0001V5100000000013SC' and pk_detail in(

select distinct pk_detail from gl_detail,gl_voucher,bd_accsubj where gl_voucher.pk_voucher = gl_detail.pk_voucher and

gl_detail.pk_accsubj = bd_accsubj.pk_accsubj and gl_voucher.pk_glorgbook = '0001V5100000000013SC'

and bd_accsubj.CASHBANKFLAG = 0)

delete from gl_cashflowcase where pk_glorgbook = '0001V5100000000013SC' and pk_detail in(

select distinct pk_detail from gl_detail,gl_voucher,bd_accsubj where gl_voucher.pk_voucher = gl_detail.pk_voucher and

gl_detail.pk_accsubj = bd_accsubj.pk_accsubj and gl_voucher.pk_glorgbook = '0001V5100000000013SC'

and bd_accsubj.CASHBANKFLAG = 0)

4.余额表翻倍

问题原因:有可能是期初执行ctrl+alt+t后,没有马上重建余额表

一定要先备份数据库

查找有问题的主体账簿主键(pk_glorgbook)

select pk_glorgbook from bd_glorgbook where glorgbookcode = ''

执行脚本,pk_glorgbook = '' 中写入有问题的主体账簿主键,注意修改相应年度

update gl_detail set debitamount=debitamount/2,creditamount=creditamount/2, fracdebitamount=fracdebitamount/2,fraccreditamount=fraccreditamount/2,

localdebitamount=localdebitamount/2,localcreditamount=localcreditamount/2,

debitquantity=debitquantity/2,creditquantity=creditquantity/2

where pk_voucher in (select pk_voucher from gl_voucher where year = ''

and voucherkind = 2 and pk_glorgbook = '' and dr = 0)

重建余额表