SQL的查询语句如下:
=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, (zys+cys)/(0.334*37*ylc) as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
想将红色字体的结果输出为百分比。如:50.00%
参考过这一句:cast(cast(分子*1.0*100/分母 as decimal(10,2)) as varchar(50)) +'%'
但将这一句加进去的时候会提示:语句中含有不能识别的短语或关键字。
8 个解决方案
#1
补充说明:VFP版本是VFP9.0,SP2;
SQL版本是SQL2000。
SQL版本是SQL2000。
#2
用这个你就熟悉了:
select ..., str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl, ...
from ...
select ..., str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl, ...
from ...
#3
=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl,
str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
是这样子吗?这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”
另外,用SQLEXEC(insert into...values...) 时, 如果字段名太长或者太多,也会出现这样的提示。
上次试过values13个字段后就错误提示,后来不得不改短字段了。
是这样子吗?这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”
另外,用SQLEXEC(insert into...values...) 时, 如果字段名太长或者太多,也会出现这样的提示。
上次试过values13个字段后就错误提示,后来不得不改短字段了。
#4
你的字符串长度超过了 255,所以报错,分成2个字符串即可,改为:
=SQLExec(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj "+"from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
=SQLExec(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj "+"from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
#5
如果你要传递的 SQL 语句太长, 检查它是否超过了 Visual FoxPro 串长的最大限度 255 字符。太长的串会产生 "命令中含有不能识别的短语或关键字。" 错误。但是, 如果你把它们分割到多个相连的串中, 你可以传递长的 SQL 语句。例如:
lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
"FROM <several tables> " + ;
"WHERE <complex filter expression>")
lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
"FROM <several tables> " + ;
"WHERE <complex filter expression>")
#6
这种情况,要用拼接字符串,如下,可能在类型上拼接上要再改
local cIn
cIn=''
cIn=cIn+"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj,"
cIn=cIn+str((zys+cys)/0.334,3)+' as zzl, '
cIn=cIn+str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' +' as szl,'
cIn=cIn+str(zys+cys)/rs +'as pj from schz '
cIn=cIn+'where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)'
#7
原来超过256个字符串也会报错!
楼上几位,回复的效率太高了!
谢谢!!
楼上几位,回复的效率太高了!
谢谢!!
#8
稍后结贴。
#1
补充说明:VFP版本是VFP9.0,SP2;
SQL版本是SQL2000。
SQL版本是SQL2000。
#2
用这个你就熟悉了:
select ..., str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl, ...
from ...
select ..., str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl, ...
from ...
#3
=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl,
str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
是这样子吗?这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”
另外,用SQLEXEC(insert into...values...) 时, 如果字段名太长或者太多,也会出现这样的提示。
上次试过values13个字段后就错误提示,后来不得不改短字段了。
是这样子吗?这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”
另外,用SQLEXEC(insert into...values...) 时, 如果字段名太长或者太多,也会出现这样的提示。
上次试过values13个字段后就错误提示,后来不得不改短字段了。
#4
你的字符串长度超过了 255,所以报错,分成2个字符串即可,改为:
=SQLExec(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj "+"from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
=SQLExec(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj "+"from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )
#5
如果你要传递的 SQL 语句太长, 检查它是否超过了 Visual FoxPro 串长的最大限度 255 字符。太长的串会产生 "命令中含有不能识别的短语或关键字。" 错误。但是, 如果你把它们分割到多个相连的串中, 你可以传递长的 SQL 语句。例如:
lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
"FROM <several tables> " + ;
"WHERE <complex filter expression>")
lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
"FROM <several tables> " + ;
"WHERE <complex filter expression>")
#6
这种情况,要用拼接字符串,如下,可能在类型上拼接上要再改
local cIn
cIn=''
cIn=cIn+"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj,"
cIn=cIn+str((zys+cys)/0.334,3)+' as zzl, '
cIn=cIn+str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' +' as szl,'
cIn=cIn+str(zys+cys)/rs +'as pj from schz '
cIn=cIn+'where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)'
#7
原来超过256个字符串也会报错!
楼上几位,回复的效率太高了!
谢谢!!
楼上几位,回复的效率太高了!
谢谢!!
#8
稍后结贴。