tSQL 查询错误：在需要条件的上下文中指定的非布尔类型的表达式，接近')'

Question

我正在尝试从 2 个表中查询并计算一个值。我正在使用他们的查询工具在感应自动化点火中执行此操作。然后使用该值，我想进行 2 次计算。 我最终发现我需要一个嵌套查询，我相信我很接近，但我在标题中得到了错误。

SELECT
[PalletTally Table 2].PartNumber,
[PalletTally Table 2].ChosenWeight,
[PalletTally Table 2].[Line Speed],
[PalletTally Table 2].PalletCount,
([PalletTally Table 2].[ChosenWeight]*[Part Pallet Info Table].[CUT 
LENGTH]/12*[Part Pallet Info Table].[PIECES PER BALE]+[Part Pallet Info 
Table].[TARE WT]) AS CalcPalletWeight,
(TotalPalletWeight / (1  + (CalcPalletWeight * PalletCount)) * [Line Speed]  
* 12 / 60) AS [Lbs./Hr.]
FROM [PalletTally Table 2]
WHERE 
(
Select


([PalletTally Table 2].[Pallet 1 Weight] +
[PalletTally Table 2].[Pallet 2 Weight] +
[PalletTally Table 2].[Pallet 3 Weight] +
[PalletTally Table 2].[Pallet 4 Weight] +
[PalletTally Table 2].[Pallet 5 Weight] +
[PalletTally Table 2].[Pallet 7 Weight] +
[PalletTally Table 2].[Pallet 6 Weight] +
[PalletTally Table 2].[Pallet 8 Weight] +
[PalletTally Table 2].[Pallet 9 Weight] +
[PalletTally Table 2].[Pallet 10 Weight] +
[PalletTally Table 2].[Pallet 11 Weight] +
[PalletTally Table 2].[Pallet 12 Weight] +
[PalletTally Table 2].[Pallet 13 Weight] +
[PalletTally Table 2].[Pallet 14 Weight] +
[PalletTally Table 2].[Pallet 15 Weight] +
[PalletTally Table 2].[Pallet 16 Weight] +
[PalletTally Table 2].[Pallet 17 Weight] +
[PalletTally Table 2].[Pallet 18 Weight] +
[PalletTally Table 2].[Pallet 19 Weight] +
[PalletTally Table 2].[Pallet 20 Weight] +
[PalletTally Table 2].[Pallet 21 Weight] +
[PalletTally Table 2].[Pallet 22 Weight] +
[PalletTally Table 2].[Pallet 23 Weight] +
[PalletTally Table 2].[Pallet 24 Weight] +
[PalletTally Table 2].[Pallet 25 Weight] +
[PalletTally Table 2].[Pallet 26 Weight] +
[PalletTally Table 2].[Pallet 27 Weight] +
[PalletTally Table 2].[Pallet 28 Weight] +
[PalletTally Table 2].[Pallet 30 Weight] +
[PalletTally Table 2].[Pallet 31 Weight] +
[PalletTally Table 2].[Pallet 29 Weight] +
[PalletTally Table 2].[Pallet 32 Weight] +
[PalletTally Table 2].[Pallet 33 Weight] +
[PalletTally Table 2].[Pallet 34 Weight] +
[PalletTally Table 2].[Pallet 35 Weight] +
[PalletTally Table 2].[Pallet 36 Weight] +
[PalletTally Table 2].[Pallet 37 Weight] +
[PalletTally Table 2].[Pallet 39 Weight] +
[PalletTally Table 2].[Pallet 40 Weight] +
[PalletTally Table 2].[Pallet 38 Weight] +
[PalletTally Table 2].[Pallet 41 Weight] +
[PalletTally Table 2].[Pallet 43 Weight] +
[PalletTally Table 2].[Pallet 42 Weight] +
[PalletTally Table 2].[Pallet 44 Weight] +
[PalletTally Table 2].[Pallet 45 Weight] +
[PalletTally Table 2].[Pallet 46 Weight] +
[PalletTally Table 2].[Pallet 47 Weight] +
[PalletTally Table 2].[Pallet 48 Weight] +
[PalletTally Table 2].[Pallet 49 Weight] +
[PalletTally Table 2].[Pallet 50 Weight] +
[PalletTally Table 2].[Pallet 51 Weight] +
[PalletTally Table 2].[Pallet 52 Weight] +
[PalletTally Table 2].[Pallet 53 Weight] +
[PalletTally Table 2].[Pallet 54 Weight] +
[PalletTally Table 2].[Pallet 55 Weight] +
[PalletTally Table 2].[Pallet 56 Weight] +
[PalletTally Table 2].[Pallet 57 Weight] +
[PalletTally Table 2].[Pallet 58 Weight] +
[PalletTally Table 2].[Pallet 59 Weight] +
[PalletTally Table 2].[Pallet 60 Weight] +
[PalletTally Table 2].[Pallet 61 Weight]) 
AS PalletWeightTotal
FROM [PalletTally Table 2]
INNER JOIN [Part Pallet Info Table] ON [Part Pallet Info Table].[PART 
NUMBER]
= [PalletTally Table 2].PartNumber
WHERE [PalletTally Table 2].[Date] > GETDATE() - 5
)

我是实际开发 SQL(在旅途中学习此内容的工业工程师(的新手，所以我完全缺少的任何信息都让我知道。

提前谢谢。

Answer 1

很难告诉你最好的解决方案，但是 - 只是为了回答你的问题 - 我会尝试减少一点以使其更明显：

SELECT
[PalletTally Table 2].PartNumber,
--more columns
FROM [PalletTally Table 2]
WHERE 
(
Select
(
[PalletTally Table 2].[Pallet 1 Weight] +
[PalletTally Table 2].[Pallet 2 Weight] +
--more of them
) AS PalletWeightTotal
FROM [PalletTally Table 2]
--some joins
WHERE [PalletTally Table 2].[Date] > GETDATE() - 5
)

基本上这意味着以下内容

SELECT Something
FROM Somewhere
WHERE 
(
Select ASumOfManyValues
FROM FromTheSameTable
WHERE SomeCondition is true
)
??????????

您正在告诉引擎过滤 SELECT。然后你计算一个值 - 就是这样...这个值 - 假设 - 应该根据边框值进行测试，例如>0.

希望这有帮助...

提示

我很确定，你的桌子设计是错误的。每当您觉得需要命名数字元素(例如pallet1，pallet2(时，这是一个指向相关边表的强指针。

如果桌子的设计在您的控制之下，那么通过适当的结构，一切都会更容易......