主题：调用SQL视图出错

一　生成查询表

Dim q As new QueryBuilde

q.Cq.TableName = "生产备料表"
q.SelectString = "select * from {IaV_STSCBlb}"
q.Build
With Tables("生产备料表")
    .Cols("iPSQuantity").GrandTotal = True
    .Cols("iPlanQuantity").GrandTotal = True
    .GrandTotal = True
End With
MainTable = Tables("生产备料表")

 

结果出现错误提示,检查后发现特别之处在于SQL视图中有除法表达式,请问是否这个原因?(其他普通视图能成功)

有什么解决办法呢?

 

CREATE VIEW dbo.IaV_STSCBlb
AS

SELECT w.cWhCode, w.cWhName, d .cDepCode, d .cDepName, CONVERT(varchar(40),
      s.cSOCode) AS cSOCode, ISNULL(m.cVouchCode, '') AS cVouchCode,
      CONVERT(varchar(30), i.cInvCode) AS cInvCode, ISNULL(i.cInvAddCode, '')
      AS cInvAddCode, i.cInvName, i.cInvStd, m.cFree1 AS bFree1, m.cFree2 AS bFree2,
      i.cInvM_Unit, m.Quantity, CONVERT(varchar(30), ivs.cInvCode) AS scInvCode,
      ISNULL(ivs.cInvAddCode, '') AS scInvAddCode, ivs.cInvName AS scInvName,
      ivs.cInvStd AS scInvStd, ms.cFree1 AS sbFree1, ms.cFree2 AS sbFree2,
      round(ms.iPlanQuantity / m.Quantity,4) AS iPSQuantity, round(ms.iPlanQuantity,4) iPlanQuantity,
      round(isnull(ms.iSumActiQuantity, 0),4) AS iSumActiQuantity,
      CASE WHEN ms.iPlanQuantity - isnull(ms.iSumActiQuantity, 0)
      >= 0 THEN round(ms.iPlanQuantity - isnull(ms.iSumActiQuantity, 0),4) ELSE 0 END AS ibl,
      m.PlanDate, m.dIntend, m.dFinished
FROM MatchVouch m LEFT JOIN
      MatchVouchs ms ON m.cVouchCode = ms.cVouchCode LEFT JOIN
      Warehouse w ON ms.cWhCode = w.cWhCode LEFT JOIN
      Department d ON m.cDepCode = d .cDepCode LEFT JOIN
      SO_SODetails s ON s.isosid = m.isosid LEFT JOIN
      SO_SOMain sm ON sm.cSOCode = s.cSOCode LEFT JOIN
      Inventory i ON i.cInvCode = m.cPSPCode LEFT JOIN
      Inventory ivs ON ivs.cInvCode = ms.cInvCode
WHERE 1 = 1 AND (m.cPSPCode <> ' ')

 

 你的视图本身报错吗？

 

 直接执行这个语句报错吗？

 

q.SelectString = "select * from {IaV_STSCBlb}"
q.Build

是不是可以用狐表来处理这个计算过程,然后就越过SQL到狐表这个"门槛"了?

 代码报什么错？

 

 1、你的视图，是否能正常通过sql语句得到？

 

 2、出错，报什么错？

在SQL中执行　select * into IaV_STSCBlb1 from IaV_STSCBlb　可以顺利生成表　IaV_STSCBlb1

然后　

q.SelectString = "select * from {IaV_STSCBlb1}"
q.Build

可以成功执行,

区别就在于越过了含有除法的表达式(结果小数位数很长)

所以估计和除法有关

 直接执行语句 select * from {IaV_STSCBlb} 是否能得到结果？

 

 1、在sqlserver执行是否可以

 

 2、在foxtable执行是否可以，如果不可以，报什么错

直接执行语句 select * from {IaV_STSCBlb} 可以得到结果？

 

 1、在sqlserver执行可以

2、在foxtable执行不可以,把表达式字段省略掉也不可以,

3　提示的图片怎么发上来?

此主题相关图片如下：错误.jpg

 你试试把sql语句在这里测试，看是否可以，如果可以，你原本的语句，尝试给表换一个名字。

 

http://www.foxtable.com/help/topics/1484.htm