SQL查询错误:在需要条件的上下文中指定了非布尔类型的表达式，靠近')‘

Question

我正在尝试从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)时，这是一个指向相关副表的强指针。

如果表格的设计在你的控制之下，只要有一个合适的结构，一切都会变得更容易…