MS Access / SQL Server 传递查询

Question

我的查询确实运行，但没有返回结果：

SET NoCount ON

SELECT 
   Inventory.EffectiveDate, 
   Inventory.Quantity, 
   Inventory.SourceType,
   Inventory.PickingLocation, 
   Inventory.SourceInventory,
   Locations.LocationId,
   Customers.CustomerName,
   Products.ProductId,
   LocationFrom.LocationId as lFrom,
   LocationTo.LocationId as lTo

FROM (((((((dbo.Inventory AS Inventory

   LEFT JOIN dbo.Products AS Products ON  Products.Product = Inventory.Product )
   LEFT JOIN dbo.Locations AS Locations ON Locations.Location = Inventory.Location )
   LEFT JOIN dbo.Customers AS Customers ON Customers.ConsignmentLocation = Inventory.Location )

   LEFT JOIN dbo.Inventory AS SourceLocation ON SourceLocation.Inventory = Inventory.SourceInventory)
   LEFT JOIN dbo.Locations AS LocationFrom ON LocationFrom.Location = SourceLocation.Location )

   LEFT JOIN dbo.Inventory AS TargetLocation ON TargetLocation.Inventory = Inventory.TargetInventory)
   LEFT JOIN dbo.Locations AS LocationTo ON LocationTo.Location = TargetLocation.Location)

WHERE  
    (Inventory.SourceType = 'Q' OR Inventory.SourceType = 'G' OR Inventory.SourceType = 'P' OR Inventory.SourceType = 'A' OR Inventory.SourceType = 'B') 
    AND 
    ((Inventory.EffectiveDate >= 2011-12-30 And Inventory.EffectiveDate <= 2011-12-31));

此查询从 Excel 运行良好。但我一直在寻找能够查看表格的工具，这就是我使用 Access 的原因 - 但这给我带来了更多问题......

Answer 1

您需要用单引号将日期参数引起来。

Inventory.EffectiveDate >= '2011-12-30'

您还应该考虑使用较短的别名以使代码更加简洁。我不确定使用 Products 这样的别名来表示 dbo.Products 的目的...如果 Access 不强制，您还应该消除所有不必要的括号他们对你。

SET NOCOUNT ON;

SELECT 
   inv.EffectiveDate, 
   inv.Quantity, 
   inv.SourceType,
   inv.PickingLocation, 
   inv.SourceInventory,
   loc.LocationId,
   cust.CustomerName,
   prod.ProductId,
   lFrom.LocationId as lFrom,
   lTo.LocationId as lTo
FROM  dbo.Inventory AS inv
LEFT OUTER JOIN dbo.Products  AS prod  ON prod.Product   = inv.Product
LEFT OUTER JOIN dbo.Locations AS loc   ON loc.Location   = inv.Location
LEFT OUTER JOIN dbo.Customers AS cust  ON inv.Location   = cust.ConsignmentLocation
LEFT OUTER JOIN dbo.Inventory AS src   ON src.Inventory  = inv.SourceInventory
LEFT OUTER JOIN dbo.Locations AS lFrom ON lFrom.Location = src.Location
LEFT OUTER JOIN dbo.Inventory AS trg   ON trg.Inventory  = inv.TargetInventory
LEFT OUTER JOIN dbo.Locations AS lTo   ON lTo.Location   = trg.Location
WHERE  
    inv.SourceType IN ('Q', 'G', 'P', 'A', 'B') 
    AND inv.EffectiveDate >= '2011-12-30' 
    AND inv.EffectiveDate <= '2011-12-31'; -- suspect you want < '2012-01-01' here
    -- unless your column doesn't store time.