|
楼主 |
发表于 2014-1-6 17:04:29
|
显示全部楼层
本帖最后由 fairyfox 于 2014-1-6 17:16 编辑
--汇总所有月结GL_Mend到一临时表
SET @field=''
SELECT @field =@field + name +',' FROM syscolumns WHERE ID=OBJECT_ID('GL_mend') AND name<>'pubufts'
SET @field=LEFT(@field,LEN(@field)-1)
SELECT * INTO #GL_mend FROM GL_mend WHERE 1=2
DECLARE rs CURSOR FAST_FORWARD FOR SELECT cDatabase,ISNULL(iEndYear,(SELECT MAX(iYear) FROM UFSystem..UA_Period WHERE cAcc_Id=@accID AND ISNULL(bIsDelete,0)=0 ))
FROM UFSystem..UA_AccountDatabase WHERE cAcc_ID=@accID AND DB_ID(cDatabase) IS NOT NULL
OPEN rs
FETCH NEXT FROM rs INTO @dbName,@maxPeriodYear
WHILE @@FETCH_STATUS=0
BEGIN
SET @strSql=' IF (SELECT CAST(cValue AS decimal) FROM '+@dbName+'..AccInformation WHERE cID=''99'' AND cSysID=''AA'')>=10.00'
SET @strSql=@strSql+' EXEC(''INSERT INTO #GL_mend('+@field+') SELECT '+@field+' FROM '+@dbName+'..GL_mend WHERE iYear<='+CAST(@maxPeriodYear AS nvarchar(10))+''')'
EXEC(@strSql)
FETCH NEXT FROM rs INTO @dbName,@maxPeriodYear
END
CLOSE rs
DEALLOCATE rs
【DECLARE rs CURSOR FAST_FORWARD FOR SELECT cDatabase,ISNULL(iEndYear,(SELECT MAX(iYear) FROM UFSystem..UA_Period WHERE cAcc_Id=@accID AND ISNULL(bIsDelete,0)=0 ))】提取的是同帐套号的好几年的数据库名称,之前升级导致2011-2012年的GL_mend数据表中的bflag_om字段全是null值,全部改成0,重新建帐套,搞定。
终于靠自己的sql知识搞定了,呵呵。
|
|