[求助]构造sql语句时发生蹊跷的错误

gerrynj 2010-09-25 11:15:46
我的思路是这样的,构造字符串sCalcFields,以此作为sql查询语句的字段。但运行时发生错误,信息如下:

Error Executing Database Query.

Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.


498 : <cfquery name="rsS" dbtype="query">
499 : select #sCalcFields# from rs
500 : </cfquery>

SQL select sum(yns_1) as yns_1,sum(yns_1c) as yns_1c,sum(yns_1a) as yns_1a,decode(sum(yns_1),0,null,sum(yns_1a)/sum(yns_1))*100 as yns_1p,sum(yns_2) as yns_2,sum(yns_2c) as yns_2c,sum(yns_2a) as yns_2a,decode(sum(yns_2),0,null,sum(yns_2a)/sum(yns_2))*100 as yns_2p from rs

但是,我在toad中,直接执行
select sum(yns_1) as yns_1,sum(yns_1c) as yns_1c,sum(yns_1a) as yns_1a,decode(sum(yns_1),0,null,sum(yns_1a)/sum(yns_1))*100 as yns_1p,sum(yns_2) as yns_2,sum(yns_2c) as yns_2c,sum(yns_2a) as yns_2a,decode(sum(yns_2),0,null,sum(yns_2a)/sum(yns_2))*100 as yns_2p from (在此将rs替换为具体的select语句),是没有问题的。

另还发现,如果将sCalcFields构造成不使用decode的字符串:如
sum(yns_1) as yns_1,sum(yns_1c) as yns_1c,sum(yns_1a) as yns_1a,sum(yns_1a)/sum(yns_1)*100 as yns_1p,sum(yns_2) as yns_2,sum(yns_2c) as yns_2c,sum(yns_2a) as yns_2a,sum(yns_2a)/sum(yns_2) as yns_2p
程序就能正常运行了,求助各位大侠,怎么回事呢?我还是希望使用decode判断的
...全文
273 点赞 收藏 7
写回复
7 条回复
Dogfish 2010年09月26日
就是连3个Query连合取数,也是不支持的.
回复 点赞
Dogfish 2010年09月26日
Query in Query的功能有限的.很多在SQL里支持的在Query in Query是不支持的.
回复 点赞
gerrynj 2010年09月26日
dogfish现身吧~
回复 点赞
gerrynj 2010年09月26日
分母为0的话则合计中的增减比取'',注意如果取null则会出现同样的错误,挺奇怪的。
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.
回复 点赞
gerrynj 2010年09月26日
只有将decode改造了一下:
<cfset tmp=sPref&iPos>
<cfquery name="rsQ" dbtype="query">
select #sCf#(#tmp#) Qtmp from rs
</cfquery>
<cfif rsQ.Qtmp neq 0>
<cfset sCalcFields=sCalcFields & "," & "(#sCf#(#tmp#a)/#sCf#(#tmp#))*100 as #tmp#p">
<cfelse>
<!---
分母为0的话则合计中的增减比取'',注意如果取null则会出现错误,挺奇怪的。如下:
<cfset sCalcFields=sCalcFields & "," & "null as #tmp#p">
--->
<cfset sCalcFields=sCalcFields & "," & "'' as #tmp#p">
</cfif>

回复 点赞
yaya203 2010年09月25日
decode(sum(yns_1),0,null,sum(yns_1a)/sum(yns_1))

你这个里面 貌似少一个参数


sum(yns_1) = 0 的时候 你显示 null
sum(yns_1) ????的时候 你显示 sum(yns_1a)/sum(yns_1)

回复 点赞
gerrynj 2010年09月25日
我的意思是这样的:
只有当sum(yns_1) = 0 的时候 返回 null
其它情况,也即默认情况下 ,返回 sum(yns_1a)/sum(yns_1)
回复 点赞
发动态
发帖子
ColdFusion
创建于2007-09-28

666

社区成员

996

社区内容

Web 开发 ColdFusion
社区公告
暂无公告