我正在尝试编辑现有的SQL RDL查询，以稍微更改软件供应商提供的报告。在尝试更改报告时，我以为我已经删除了对上一个等式的所有引用，但现在我看到以下错误消息
数据集"主数据源"(rsErrorExecutingCommand(的查询执行失败关键字"ON">
附近的语法不正确
有没有办法准确找到代码中错误存在的位置，搜索关键字"ON"似乎并不成功。
代码非常大，这就是为什么我没有在这里发布的原因，但如果这是唯一的回答方法，我可以吗？
干杯
DECLARE @EmptyGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000'
IF OBJECT_ID('tempdb..#Cycles') IS NOT NULL DROP TABLE #Cycles
CREATE TABLE #Cycles
(
Id uniqueidentifier
,CycleStart datetime
,CycleEnd datetime
,TruckId uniqueidentifier
,LoadWeight int
,LoadDuration int
,LoadStart datetime
,LoaderId uniqueidentifier
,PRIMARY KEY (Id)
,UNIQUE (CycleStart, Id)
,UNIQUE (CycleEnd, Id)
,UNIQUE (LoadStart, Id)
,UNIQUE (LoaderId, Id)
)
IF OBJECT_ID('tempdb..#History') IS NOT NULL DROP TABLE #History
CREATE TABLE #History
(
Id uniqueidentifier
,MachineId uniqueidentifier
,ActivityId uniqueidentifier
,StartDatetime datetime
,EndDatetime datetime
,PRIMARY KEY (Id)
,UNIQUE (MachineId, Id)
,UNIQUE (ActivityId, Id)
,UNIQUE (StartDatetime, Id)
,UNIQUE (EndDatetime, Id)
)
IF OBJECT_ID('tempdb..#CycleIds') IS NOT NULL DROP TABLE #CycleIds
CREATE TABLE #CycleIds
(
Id uniqueidentifier
,PRIMARY KEY(Id)
)
IF OBJECT_ID('tempdb..#NumberedCycleLoaders') IS NOT NULL DROP TABLE #NumberedCycleLoaders
CREATE TABLE #NumberedCycleLoaders
(
Id uniqueidentifier
,LoaderId uniqueidentifier
,RowNumber int
,UNIQUE (LoaderId, Id)
,UNIQUE (RowNumber, Id)
)
-- Get the site defined activities for shutdown and idle for all loading unit types.
DECLARE @LoaderDownActivities TABLE (Id uniqueidentifier, PRIMARY KEY (Id))
INSERT INTO @LoaderDownActivities
SELECT DISTINCT a.Id
FROM Activity a
INNER JOIN OnboardActivityDefault oad ON a.Id = oad.ActivityId
INNER JOIN OnboardActivity oa ON oad.OnboardActivityId = oa.Id
WHERE -- onboard activity for Shutdown and Idel
oa.Id IN ('D8FE016D-9457-466A-8866-15678FFC3F35', 'CC704887-0667-4950-9491-75CF761E7A2A')
-- machine types for loader and excavator
AND oad.MachineTypeId IN ('E32A4E32-E453-462B-AED6-D2A87EE29AF7', '04733AA5-51A0-41E8-8B23-13FE9C85221D')
/* ******************************************************** */
-- First get the Ids from the cycle table to limit the strain on the join to Running History
INSERT INTO #CycleIds
SELECT Id
FROM TruckCycle
WHERE CycleStart >= @ReportStartUTC
AND CycleStart < @ReportEndUTC
UNION ALL
-- Also get the last cycle before the reporting period.
SELECT Id
FROM (
SELECT Id
,RowNumber = ROW_NUMBER() OVER (PARTITION BY TruckId ORDER BY CycleStart DESC)
FROM TruckCycle
WHERE CycleStart < @ReportStartUTC
) sq
WHERE RowNumber = 1
-- Get the Truck Loading History - this is so we can determine the time at which each loading period occurred
-- Which will give us the operator of the loading unit
;WITH truckLoadingActivities AS (
SELECT DISTINCT a.Id
FROM Activity a
INNER JOIN OnboardActivityDefault oad ON a.Id = oad.ActivityId
INNER JOIN OnboardActivity oa ON oad.OnboardActivityId = oa.Id
WHERE -- onboard activity for Loading
oa.Id = '9ADBE536-C112-4E6E-9EA4-AE4973FA9C98'
-- machine types for Truck and Road Train
AND oad.MachineTypeId IN ('16D31A04-0377-4AC5-AE40-917E1516E54E', 'B9688837-261F-42F7-8545-C451245D842F')
)
INSERT INTO #History (Id, MachineId, StartDatetime, EndDatetime)
SELECT h.Id
,h.MachineId
,h.StartDatetime
,h.EndDatetime
FROM RunningHistory h
INNER JOIN truckLoadingActivities a ON h.ActivityId = a.Id
WHERE -- Also limit the history on loading activities that occurred during the period.
h.StartDatetime >= @ReportStartUTC
AND h.StartDatetime < @ReportEndUTC
-- Now extract the cycle information, along with the first loading timestamp of the truck involved in each cycle.
INSERT INTO #Cycles (Id, CycleStart, CycleEnd, TruckId, LoadWeight, LoadDuration, LoadStart)
SELECT c.Id
,c.CycleStart
,c.CycleEnd
,c.TruckId
,c.LoadWeight
,c.LoadDuration
,MIN(h.StartDatetime) as LoadStart
FROM TruckCycle c
INNER JOIN #CycleIds cids ON c.Id = cids.Id
INNER JOIN #History h
ON c.TruckId = h.MachineId
AND (h.StartDatetime BETWEEN c.CycleStart AND c.CycleEnd)
GROUP BY
c.Id
,c.CycleStart
,c.CycleEnd
,c.TruckId
,c.LoadWeight
,c.LoadDuration
-- To determine the loader for each cycle, we have to select one of many.
-- Do a simple selection by numbering each loader associated with each cycle, and then pick the first one (ordered by loader description)
INSERT INTO #NumberedCycleLoaders (Id, LoaderId, RowNumber)
SELECT CycleId
,cl.LoaderId
,ROW_NUMBER() OVER (PARTITION BY CycleId ORDER BY l.Description ASC) AS LoaderNumber
FROM TruckCycleLoader cl
INNER JOIN #CycleIds c ON cl.CycleId = c.Id
INNER JOIN Machine l ON l.Id = cl.LoaderId
-- Now update the cycle information with the loaders
UPDATE c
SET c.LoaderId = cl.LoaderId
FROM #Cycles c
INNER JOIN #NumberedCycleLoaders cl ON c.Id = cl.Id
ON cl.LoaderId = op.MachineId
AND (c.LoadStart BETWEEN op.StartDatetime AND op.EndDatetime)
WHERE cl.RowNumber = 1
-- Remove cycles that have been filtered out by the user - to limit the next lot of queries.
DELETE FROM #Cycles
WHERE (@MachineId IS NOT NULL AND LoaderId <> @MachineId)
AND (@MachineTypeId IS NOT NULL AND LoaderId NOT IN (SELECT Id FROM Machine WHERE MachineTypeId = @MachineTypeId))
AND (@MachineGroupId IS NOT NULL AND (LoaderId NOT IN (SELECT MachineId FROM MachineGroupMachine WHERE MachineGroupId = @MachineGroupId)))
-- Clear history table
DELETE FROM #History
-- Get the Running History records for each loader over the reporting period.
INSERT INTO #History (Id, MachineId, ActivityId, StartDatetime, EndDatetime)
SELECT h.Id
,h.MachineId
,h.ActivityId
,h.StartDatetime
,ISNULL(CASE WHEN h.EndDatetime > @ReportEndUTC THEN @ReportEndUTC ELSE h.EndDatetime END, @ReportEndUTC) as EndDatetime
FROM RunningHistory h
WHERE h.MachineId IN (SELECT LoaderId FROM #Cycles)
AND h.StartDatetime >= @ReportStartUTC
AND h.StartDatetime < @ReportEndUTC
-- The previous query retrieved all activities up to the end of the period, however
-- it will most likely have missed the first activity history (unless it started exactly on the reporting period start).
-- Include the last activity before the reporting period for each loader.
INSERT INTO #History (Id, MachineId, ActivityId, StartDatetime, EndDatetime)
SELECT h.Id
,h.MachineId
,h.ActivityId
,CASE WHEN h.StartDatetime < @ReportStartUTC THEN @ReportStartUTC ELSE h.StartDatetime END as StartDatetime
,ISNULL(CASE WHEN h.EndDatetime > @ReportEndUTC THEN @ReportEndUTC ELSE h.EndDatetime END, @ReportEndUTC) as EndDatetime
FROM (
SELECT Id
,StartDatetime
,ISNULL(EndDatetime, @ReportEndUTC) as EndDatetime
,MachineId
,ActivityId
,OnboardActivityId
,ROW_NUMBER() OVER (PARTITION BY MachineId ORDER BY StartDatetime DESC) AS LoaderHistoryNumber
FROM RunningHistory
WHERE MachineId IN (SELECT LoaderId FROM #Cycles)
-- Use Less than so we don't include anything retrieved from the above query
AND StartDatetime < @ReportStartUTC
) as h
WHERE LoaderHistoryNumber = 1
-- Get the total time recorded for each loading unit combination
-- The loading units are filtered by the cycles selected (see query above)
DECLARE @LoaderTimes TABLE
(
Id uniqueidentifier
,DelayDuration int NULL
,ProductionDuration int NULL
,TotalDuration int NULL
)
INSERT INTO @LoaderTimes (Id, TotalDuration)
SELECT h.MachineId
,SUM(DATEDIFF(second, h.StartDatetime, ISNULL(h.EndDatetime, @ReportEndUTC))) as LoaderTotalTime
FROM #History h
GROUP BY
h.MachineId
-- Now Get the time each loader and operator spends in Delay/Down activities over the reporting period.
UPDATE lt
SET lt.DelayDuration = ISNULL(Duration, 0)
FROM @LoaderTimes lt
INNER JOIN
(
SELECT _lt.Id
,SUM(DATEDIFF(second, _h.StartDatetime, ISNULL(_h.EndDatetime, @ReportEndUTC))) as Duration
FROM @LoaderTimes _lt
INNER JOIN #History _h ON _h.MachineId = _lt.Id
INNER JOIN Activity _a ON _h.ActivityId = _a.Id
LEFT JOIN @LoaderDownActivities _lda ON _h.ActivityId = _lda.Id
WHERE (
-- Delay activity type
_a.ActivityTypeId = '9BA50462-B007-4383-AA86-4427958867E2'
-- or a loader down activity (shutdown / idle)
OR _lda.Id IS NOT NULL
)
GROUP BY
_lt.Id
) lt2 ON lt.Id = lt2.Id
-- Now update the production time in the temporary LoaderTimes table to be Total - Delay.
UPDATE @LoaderTimes
SET ProductionDuration = TotalDuration - ISNULL(DelayDuration, 0)
-- Get the final results.
SELECT LoaderId
,l.Description as LoaderDescription
,lt.TotalDuration
,DelayDuration
,ProductionDuration
,count(*) as LoadCount
,SUM(LoadWeight) as TotalWeight
,SUM(LoadDuration) as TotalLoadDuration
,SUM(CONVERT(decimal(19,4), LoadWeight)) / SUM(CONVERT(decimal(19,4), CASE LoadDuration WHEN 0 THEN NULL ELSE LoadDuration END)) * 3600.0 as InstantaneousDigRate
,CASE ProductionDuration WHEN 0 THEN 0 ELSE SUM(CONVERT(decimal(19,4), LoadWeight)) / ProductionDuration * 3600.0 END as AverageProductivityDigRate
FROM #Cycles c
INNER JOIN Machine l ON c.LoaderId = l.Id
-- We can join on loader times even though we are performing a grouping, this is because
-- the loader times table has a unique entry for each loader combination.
LEFT JOIN @LoaderTimes lt ON lt.Id = c.LoaderId
WHERE (@MachineId IS NULL OR c.LoaderId = @MachineId)
AND (@MachineTypeId IS NULL OR l.MachineTypeId = @MachineTypeId)
AND (@MachineGroupId IS NULL OR (c.LoaderId IN (SELECT MachineId FROM MachineGroupMachine WHERE MachineGroupId = @MachineGroupId)))
GROUP BY
LoaderId
,l.Description
,lt.TotalDuration
,lt.DelayDuration
,lt.ProductionDuration
ORDER BY
l.Description
IF OBJECT_ID('tempdb..#Cycles') IS NOT NULL DROP TABLE #Cycles
IF OBJECT_ID('tempdb..#History') IS NOT NULL DROP TABLE #History
IF OBJECT_ID('tempdb..#CycleIds') IS NOT NULL DROP TABLE #CycleIds
IF OBJECT_ID('tempdb..#NumberedCycleLoaders') IS NOT NULL DROP TABLE #NumberedCycleLoaders```
此查询看起来错误。它不应该有两个ON子句：
UPDATE c
SET c.LoaderId = cl.LoaderId
FROM #Cycles c
INNER JOIN #NumberedCycleLoaders cl ON c.Id = cl.Id
ON cl.LoaderId = op.MachineId
AND (c.LoadStart BETWEEN op.StartDatetime AND op.EndDatetime)
WHERE cl.RowNumber = 1
您可能希望：
UPDATE c
SET c.LoaderId = cl.LoaderId
FROM #Cycles c
INNER JOIN #NumberedCycleLoaders cl ON c.Id = cl.Id
AND cl.LoaderId = op.MachineId -- maybe AND or OR in this line
AND (c.LoadStart BETWEEN op.StartDatetime AND op.EndDatetime)
WHERE cl.RowNumber = 1