只有通过调度程序运行作业时才会发生这种情况,直接运行时效果良好。
DELETE FROM [NAVQueriesDB].[dbo].[New Items on Order] GO
SET ANSI_WARNINGS OFF
GO
INSERT [NAVQueriesDB].[dbo].[New Items on Order] (
[Updated Date],
[Location Code],
[Order No_],
[Item No_],
[Item Description],
[Variant Code],
[Description 2],
[Outstanding Qty_],
[Order Date],
[Vendor_No],
[Vendor_Name])
SELECT
GETDATE() AS "Updated Date",
[Purch Line].[Location Code],
[Purch Line].[Document No_],
[Item].[No_],
[Item].[Description],
ISNULL([Purch Line].[Variant Code],'-') AS "Variant Code",
ISNULL([Variant].[Description 2],'-') AS "Description 2",
CAST(SUM([Purch Line].[Outstanding Quantity]) as decimal(18,0)) AS "Outstanding Qty.",
[Purch Line].[Order Date],
[Purch Line].[Buy-from Vendor No_],
[Vendor].[Name]
FROM [Wings$Item] AS "Item"
LEFT JOIN [Wings$Purchase Line] AS "Purch Line"
ON [Item].[No_] = [Purch Line].[No_]
LEFT JOIN [Wings$Item Variant] AS "Variant"
ON [Item].[No_] = [Variant].[Item No_] AND [Purch Line].[Variant Code] = [Variant].[Code]
LEFT JOIN [Wings$Vendor] AS "Vendor"
ON [Purch Line].[Buy-from Vendor No_] = [Vendor].[No_]
LEFT JOIN [Wings$Item Ledger Entry] AS "Item Ledger"
ON [Item].[No_] = [Item Ledger].[Item No_]
WHERE
[Item].[No_] > '5618%'
AND [Item].[No_] <> 'Z999999'
AND [Item Ledger].[Item No_] IS NULL
GROUP BY
[Purch Line].[Location Code],
[Purch Line].[Document No_],
[Item].[No_],
[Item].[Description],
[Purch Line].[Variant Code],
[Variant].[Description 2],
[Purch Line].[Outstanding Quantity],
[Purch Line].[Order Date],
[Purch Line].[Buy-from Vendor No_],
[Vendor].[Name]
ORDER BY
[Location Code],
[Item].[No_],
[Item].[Description],
[Purch Line].[Variant Code]
