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

[gerrynj]

我的思路是这样的，构造字符串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判断的

[gerrynj]

我的意思是这样的：
只有当sum(yns_1) = 0 的时候 返回 null
其它情况，也即默认情况下 ，返回 sum(yns_1a)/sum(yns_1)

[yaya203]

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]

只有将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>

[gerrynj]

分母为0的话则合计中的增减比取'',注意如果取null则会出现同样的错误,挺奇怪的。
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.

[gerrynj]

dogfish现身吧~

[Dogfish]

Query in Query的功能有限的.很多在SQL里支持的在Query in Query是不支持的.

[Dogfish]

就是连3个Query连合取数,也是不支持的.