查询语句又出现错误。。（大哥们过来帮下忙。呵呵）

显示全部楼层 · 2009-1-4 14:52:28

今天做了一个采购订单的未交物料数量，统计定单数量，入库数量，退货物数量
Select T4.[DocNum],T4.[NumAtCard], T4.[ItemCode],t4.quantity 订单数量,t5.quantity 已交货数量,T4.QUANTITY-t5.quantity 未交数量 from
(SELECT T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantity FROM OPOR T0INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntryGROUP BY T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode]) t4
LEFT JOIN
(Select T2.[BaseRef], T2.[ItemCode],isnull(t2.quantity,0)-isnull(t3.quantity,0) quantity from
(SELECT T0.[DocNum], T1.[BaseRef], T1.[ItemCode],sum(T1.[Quantity]) quantity FROM OPDN T0INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntryGROUP BY T0.[DocNum], T1.[BaseRef], T1.[ItemCode]) t2
left join
(SELECT T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantityFROM [dbo].[RPD1]T1GROUP BY T1.[BaseRef], T1.[ItemCode]) t3 on t2.docnum=t3.baseref and t2.itemcode=t3.itemcode ) T5 on t5.baseref=t4.docnum and t4.itemcode=t5.itemcodewhere T4.[NumAtCard]=[%0]
错误提示：必须指定要从中选取的资料表？这个错误提示什么很经常出现，这是为什么
很经常在创建查询时会提示错误，SQL语句感觉都没问题啊，但是就是提示错误，不知道SB1对SQL格式有没做什么要求？
[ 本帖最后由 linguojin 于 2009-12-19 17:23 编辑 ]

千问 · 2009-1-4 14:52:28

Select T4.[DocNum],T4.[NumAtCard], T4.[ItemCode],t4.quantity 订单数量,t5.quantity 已交货数量,T4.QUANTITY-t5.quantity 未交数量 from
(SELECT T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantity FROM OPOR T0INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntrywhere [NumAtCard]=[%0]GROUP BY T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode]) t4
LEFT JOIN
(Select T2.[BaseRef], T2.[ItemCode],isnull(t2.quantity,0)-isnull(t3.quantity,0) quantity from
(SELECT T0.[DocNum], T1.[BaseRef], T1.[ItemCode],sum(T1.[Quantity]) quantity FROM OPDN T0INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntryGROUP BY T0.[DocNum], T1.[BaseRef], T1.[ItemCode]) t2
left join
(SELECT T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantityFROM [dbo].[RPD1]T1GROUP BY T1.[BaseRef], T1.[ItemCode]) t3 on t2.docnum=t3.baseref and t2.itemcode=t3.itemcode ) T5 on t5.baseref=t4.docnum and t4.itemcode=t5.itemcode
*****
用以上试试

千问 · 2009-1-4 14:52:28

奇怪了。是没显示那错误了。。这是什么回事？大哥能否透露下。。呵呵
不知道你那边运行了没，运行时出现错误：‘NVARCHAR转为INT出错’，于是我将语句改成
where [NumAtCard]=cast([%0] as varchar(100)) ，
但是有个问题是执行时显示的参数名称却是“单据代码”，按道理应该是“客户/供应商参考编号”才对？有时候其他地方也会出现类似问题。。
[ 本帖最后由 linguojin 于 2009-12-19 17:16 编辑 ]

千问 · 2009-1-4 14:52:28

declare @a varchar(100)
/*select@a=T0.[NumAtCard]from opor t0 where T0.[NumAtCard]=[%0]*/
set @a=[%0]
Select T4.[DocNum],T4.[NumAtCard], T4.[ItemCode],t4.quantity 订单数量,t5.quantity 已交货数量,T4.QUANTITY-t5.quantity 未交数量 from
(SELECT T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantity FROM OPOR T0INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry where [NumAtCard]=@a GROUP BY T0.[DocNum],T0.[NumAtCard], T1.[BaseRef], T1.[ItemCode]) t4
LEFT JOIN
(Select T2.[BaseRef], T2.[ItemCode],isnull(t2.quantity,0)-isnull(t3.quantity,0) quantity from
(SELECT T0.[DocNum], T1.[BaseRef], T1.[ItemCode],sum(T1.[Quantity]) quantity FROM OPDN T0INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntryGROUP BY T0.[DocNum], T1.[BaseRef], T1.[ItemCode]) t2
left join
(SELECT T1.[BaseRef], T1.[ItemCode], sum(T1.[Quantity]) quantityFROM [dbo].[RPD1]T1GROUP BY T1.[BaseRef], T1.[ItemCode]) t3 on t2.docnum=t3.baseref and t2.itemcode=t3.itemcode ) T5 on t5.baseref=t4.docnum and t4.itemcode=t5.itemcode
语句换成这样后又是提示“必须要指定丛中选取的表”，真是纳闷。。B1到底对语句有什么限制，真是不得其解
不知道其他大哥能不能一起看下帮帮忙

千问 · 2009-1-4 14:52:28

我也遇到过，你把[dbo].、[]这些去掉，试试

千问 · 2009-1-4 14:52:28

恩。。。我好好试下。。HOHO

千问 · 2009-1-4 14:52:28

去掉之后可以了
不过我调试了半天，同一个语句有时候就行，有时候就不行。刚开始时提示NVARCHAR转为INT错误，后来重新创建查询之后就又好了
真搞不懂，真不清楚SB1的内部要求。
谢谢古雨兄
[ 本帖最后由 linguojin 于 2009-12-21 17:24 编辑 ]

千问 · 2009-1-4 14:52:28

NVARCHAR转为INT错误:在数据库中的字段类型不匹配时，Nvarchar类型存储空间大小与Int类型存储空间大小不一样，所以会提示那个错误。建议将用到Docentry的地方检察一下，将Int型加cast转为成nvarchar类型就好了，我也遇到了，这样处理的。

千问 · 2009-1-4 14:52:28

恩。。我以后注意下
很奇怪，有时候会有时候就不会很摸不透