[求助]构造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判断的
...全文
333 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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)

879

社区成员

发帖
与我相关
我的任务
社区描述
Web 开发 ColdFusion
社区管理员
  • ColdFusion社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧