sql视图 + pb crolltab 数据窗口问题
基于视图写了个crosstab窗口,运行报错:
“ox7c9210e6” 指令引用的“0x00000008”错,该内存不能为written
视图代码如下:下面代码在查询分析器里运行,数据量大时,也很慢
SELECT TOP 100 PERCENT dbo.ps_kcinstockd.balance, dbo.rs_department.name,
dbo.ps_kcinstockd.deptno, dbo.ps_dept.deptname, dbo.ps_kcinstockd.partno,
dbo.ps_parts.partna, dbo.ps_parts.spec, dbo.ps_parts.unit1, dbo.ps_parts.ptype1,
dbo.ps_kcinstockt.sbdate, dbo.ps_kcinstockt.shdate,
dbo.ps_kcinstockt.intype + dbo.ps_intype.intypename AS intype,
SUM(dbo.ps_kcinstockd.amount) AS amount, SUM(dbo.ps_kcinstockd.dmoney)
AS dmoney
FROM dbo.ps_kcinstockd INNER JOIN
dbo.ps_kcinstockt ON
dbo.ps_kcinstockd.billno = dbo.ps_kcinstockt.billno INNER JOIN
dbo.ps_intype ON
dbo.ps_kcinstockt.intype = dbo.ps_intype.iprimary LEFT OUTER JOIN
dbo.ps_parts ON
dbo.ps_kcinstockd.partno = dbo.ps_parts.iprimary LEFT OUTER JOIN
dbo.rs_department ON
dbo.ps_kcinstockt.balance = dbo.rs_department.iprimary LEFT OUTER JOIN
dbo.ps_dept ON dbo.ps_kcinstockt.deptno = dbo.ps_dept.iprimary
GROUP BY dbo.ps_kcinstockd.balance, dbo.rs_department.name,
dbo.ps_kcinstockd.deptno, dbo.ps_dept.deptname, dbo.ps_kcinstockd.partno,
dbo.ps_parts.partna, dbo.ps_parts.spec, dbo.ps_parts.unit1, dbo.ps_parts.ptype1,
dbo.ps_kcinstockt.sbdate, dbo.ps_kcinstockt.shdate, dbo.ps_kcinstockt.intype,
dbo.ps_intype.intypename
ORDER BY dbo.ps_parts.partna, dbo.ps_parts.spec, dbo.ps_parts.unit1
[解决办法]
放弃crosstab 吧
dbo.rs_department 貌似没杀用,用DDW吧
dbo.ps_dept 貌似没杀用,用DDW吧
ORDER BY dbo.ps_parts.partna, dbo.ps_parts.spec, dbo.ps_parts.unit1
对PB影响不大,但对数据库影响狠大
GROUP BY 隐含了ORDER ,GROUP BY 的列序 与 ORDER BY的列序 相同,你就可以砍掉它
当年我是一边骂一边写的,程序是上半年验收的(用户年初一立项,申请了经费立刻被骗来),
年底出报表时,用不成了
这大一篇代码呀,写的