简单账表自动生成的汇总select提示not support type:bigint
金蝶云社区-quHongan
quHongan
0人赞赏了该文章 889次浏览 未经作者许可,禁止转载编辑于2015年11月26日 15:32:46

自己二开的简单账表,在GetSummaryColumsSQL方法中用编写汇总代码
sb.AppendFormat("SUM(convert(bigint,FSTAYTIME)) AS {0}", field.Key);
执行时出现错误提示如下,因为FSTAYTIME字段数值较大所以必须用bigint类型,而简单账表好像不支持,如果是自己写的select语句可以用/*dialect*/实现数据库方言,而汇总的select语句是自动生成的,我只能写汇总的表达式,这种情况请问怎样解决?谢谢。

查询出现异常。Kingdee.BOS.SQL.Exception.ParserException: format sql error. target database is 'MS SQL Server' detail message is :
not support type:bigint
source sql is :
SELECT convert(varchar,'共')+convert(varchar,count(*))+convert(varchar,'人') AS FUSERNAME,SUM(FBILLCOUNT) AS FBILLCOUNT,SUM(convert(bigint,FSTAYTIME)) AS FSTAYTIME,MAX(FMAXSTAYTIME) AS FMAXSTAYTIME,MIN(FMINSTAYTIME) AS FMINSTAYTIME,SUM(convert(bigint,FSTAYTIME))/SUM(FBILLCOUNT) AS FAVGSTAYTIME,convert(varchar, SUM(convert(bigint,FSTAYTIME)) /86400)+convert(varchar,'天')+convert(varchar, sum(convert(bigint,FSTAYTIME)) %86400/3600)+convert(varchar,'小时')+convert(varchar, sum(convert(bigint,FSTAYTIME)) %86400%3600/60)+convert(varchar,'分钟') AS FDDHHMMSS,convert(varchar, MAX(FMAXSTAYTIME) /86400)+convert(varchar,'天')+convert(varchar, MAX(FMAXSTAYTIME) %86400/3600)+convert(varchar,'小时')+convert(varchar, MAX(FMAXSTAYTIME) %86400%3600/60)+convert(varchar,'分钟') AS FMAXDDHHMMSS,convert(varchar, MIN(FMINSTAYTIME) /86400)+convert(varchar,'天')+convert(varchar, MIN(FMINSTAYTIME) %86400/3600)+convert(varchar,'小时')+convert(varchar, MIN(FMINSTAYTIME) %86400%3600/60)+convert(varchar,'分钟')+convert(varchar,MIN(FMINSTAYTIME) %60)+convert(varchar,'秒') AS FMINHHMMSS,convert(varchar, SUM(convert(bigint,FSTAYTIME))/SUM(FBILLCOUNT) /86400)+convert(varchar,'天')+convert(varchar, SUM(convert(bigint,FSTAYTIME))/SUM(FBILLCOUNT) %86400/3600)+convert(varchar,'小时')+convert(varchar, SUM(convert(bigint,FSTAYTIME))/SUM(FBILLCOUNT) %86400%3600/60)+convert(varchar,'分钟')+convert(varchar,SUM(convert(bigint,FSTAYTIME))/SUM(FBILLCOUNT) %60)+convert(varchar,'秒') AS FAVGHHMMSS FROM TMP2F1B8600940F11E5A46A485AB6D

Server stack trace:
在 Kingdee.BOS.SQL.TransUtil.format(String ksql, IList stmtCol, Int32 targetDbType, FormatOptions options)
在 Kingdee.BOS.App.Data.AbstractDatabase.CreateCommandByCommandType(CommandType commandType, String commandText, Boolean needTranslate, Int32 commandTimeout)
在 Kingdee.BOS.App.Data.AbstractDatabase.CreateCommandByCommandType(CommandType commandType, String commandText, Int32 commandTimeout)
在 Kingdee.BOS.App.Data.DBUtils.ExecuteDataSet(Context ctx, String strSQL, List`1 paramList)
在 Kingdee.BOS.App.Data.DBUtils.ExecuteDataSet(Context ctx, String strSQL)

Exception rethrown at [0]:
在 System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
在 System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
在 Kingdee.BOS.Contracts.IDBService.ExecuteDataSet(Context ctx, String strSQL)
在 Kingdee.BOS.Contracts.Report.SysReportBaseService.GetListData(String sSQL)
在 Kingdee.BOS.App.Core.PlugInProxy.SysReportServicePlugInProxy.SetReportData(ReportServiceParameter parameter, IReportData report, AbstractSysReportServicePlugIn plugIn)
在 Kingdee.BOS.App.Core.PlugInProxy.SysReportServicePlugInProxy.GetReportData(ReportServiceParameter parameter)
在 Kingdee.BOS.App.Core.SysReportService.GetReportData(ReportServiceParameter parameter)

Exception rethrown at [1]:
在 System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
在 System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
在 Kingdee.BOS.Contracts.ISysReportService.GetReportData(ReportServiceParameter parameter)
在 Kingdee.BOS.ServiceHelper.SysReportServiceHelper.GetReportData(ReportServiceParameter parameter)
在 Kingdee.BOS.Model.Report.SysReportModel.GetData(Int32 start, Int32 rows)
在 Kingdee.BOS.Web.Report.SysReportView.GetListData()
在 Kingdee.BOS.Web.Report.SysReportView.<>c__DisplayClass3d.b__39()