declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when a.wzmc='''+wzmc+''' then c.sl*c.jhj else 0 end) as ['+wzmc+']'
from
(select wzmc from wzdm where len(wzdm)=3) tset @sql='select b.mc as [单位/类别], '
+@sql
+',sum(c.sl*c.jhj)*0.05 as 管理费,sum(c.sl*c.jhj)*1.05 as 合计'
+'from wzdm a,dwdm b,lld c '
+'where len(a.wzdm)=3 and left(c.wzdm,3)=a.wzdm and left(c.lldw,3)=b.dm and c.flrq between ''2010-01-01'' and ''2010-01-31'' and c.scbz<>1 and c.lldlb in(11,21,12,22)'
+'group by b.mc'
--print @sqlexec (@sql)
select
@sql=isnull(@sql+',','')
+'sum(case when a.wzmc='''+wzmc+''' then c.sl*c.jhj else 0 end) as ['+wzmc+']'
from
(select wzmc from wzdm where len(wzdm)=3) tset @sql='select b.mc as [单位/类别], '
+@sql
+',sum(c.sl*c.jhj)*0.05 as 管理费,sum(c.sl*c.jhj)*1.05 as 合计'
+'from wzdm a,dwdm b,lld c '
+'where len(a.wzdm)=3 and left(c.wzdm,3)=a.wzdm and left(c.lldw,3)=b.dm and c.flrq between ''2010-01-01'' and ''2010-01-31'' and c.scbz<>1 and c.lldlb in(11,21,12,22)'
+'group by b.mc'
--print @sqlexec (@sql)
解决方案 »
- Oracle11安装时弹出Enterprise Manager 配制失败,查看日志文件如下:求解决方案?
- Oracle trigger 问题
- 求助:Oracle9i 如何实现下面的查找
- 大家介绍一些visualc++进行oracle数据库编程的学习资料
- 表中有50多万数据 查询速度极慢 请oracle高手帮忙解决下
- 急!求助,表空间的问题
- oracle8i安装的问题
- 100分请教:如何统计查询,需要嵌套吗?
- oracle jdbc 问题----刚刚学,请大家多帮忙,小妹感激不尽!
- oracle816:在启动OracleoraHomeManagementServer时提示:在本地计算机无法启动OracleoraHomeManagementServer服务。错误997:重叠I/O操作
- 关于热备 ,oracle 数据库备份,
- win7安装oracle10g完成后登录时输入密码和用户名后界面就关闭
另外,改造成Oracle的话,给你写个大概:
set serveroutput on --设置开关,为了下面使用dbms_output.put_line输出
declare
sql_text varchar2(8000) := '';--变量定义以及使用,不需要t-sql中的"@"
begin --拼串的时候用"||"连接,赋值用":="
--第一处拼串的地方
sql_text := 'select sum(case when a.wzmc = ' || wzmc || 'then c.sl*c.jhj else 0
end...'; --后面的你自己拼一下
--第二处拼串的地方
sql_text := sql_text || 'select b.mc as [单位/类别], ....
--打印拼完的串
dbms_output.put_line(sql_text);
--执行拼完的串
EXECUTE immediate sql_text into 变量1,变量2 ..;--看看你的select语句会选出几个值,分别与INTO后面的变量对应,其中,变量1,变量2需要在declare与begin之间定义
end;
sql_text := sql_text || 'select b.mc as [单位/类别], ....
这一行后面少了个引号。。
不过,没关系。有环境的话,试一下就行。
declare
sql_text varchar2(8000) := '';--变量定义以及使用,不需要t-sql中的"@"
begin --拼串的时候用"||"连接,赋值用":="
--第一处拼串的地方
sql_text := 'select sum(case when w.wzmc = ' || wzmc || 'then l.sl*l.jhj else 0
end'||wzmc|| 'from (select wzmc from scb_wzdm where length(wzdm)=3) '
sql_text := sql_text || 'select d.dwmc dw,sum(l.sl*l.jhj)*0.05 glf,sum(l.sl*l.jhj)*1.05 hj'
||'from scb_wzdm w,scb_dwdm d,scb_lld l '
||'where length(w.wzdm)=3 and substr(l.wzdm,1,3)=w.wzdm and substr(l.lldw,1,3)=d.dwdm and to_char(l.flrqm,'yyyy-mm-dd') between '2010-01-01' and '2010-01-31' and l.scbz<>1 and l.lldlb in(11,21,12,22)'
||'group by d.dwmc' --打印拼完的串
dbms_output.put_line(sql_text);
--执行拼完的串
EXECUTE immediate sql_text into dw,glf;--看看你的select语句会选出几个值,分别与INTO后面的变量对应,其中,变量1,变量2需要在declare与begin之间定义
end;
ORA-06550: line 8, column 3:
PLS-00103: Encountered the symbol "SQL_TEXT" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol ";" was substituted for "SQL_TEXT" to continue.
ORA-06550: line 10, column 116:
PLS-00103: Encountered the symbol "YYYY" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMU
ORA-06550: line 10, column 126:
PLS-00103: Encountered the symbol ") between " when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between |
ORA-06550: line 10, column 148:
PLS-00103: Encountered the symbol " and " when expecting one of the following: ) , * & | = - + < / > at in is mod
弄了这些代码出来,但是报错,请教兰兰,是什么原因.