1
I’m in trouble where I use {Routes}.[Qualquer] us subselects.
The error returned is
"ROUTES"." COLUMN": invalid Identifier.
I tried to expose the column on select but I still have the same mistake.
SELECT /* ROUTEID */
ROUTES.[RouteId] AS ROUTEID,
/* ROUTE STARTDATETIME */
ROUTES.[DateTime] AS ROUTESTARTDATETIME,
/* ROUTE DESCRIPTION */
{Route}.[Description],
/* NAME OF THE DRIVER */
{Driver}.[Name],
/* VEHICLE LICENSE PLATE */
{Vehicle}.[Registration],
/* QUANTITY OF STOPS */
(SELECT COUNT({RouteStop}.[RouteId])
FROM {RouteStop}
WHERE {RouteStop}.[RouteId] = ROUTEID) AS STOPCOUNT,
/* AMOUNT OF FUEL */
(SELECT SUM(FUEL.[Value])
FROM {GPS} FUEL
WHERE ROUTES.[AvailabilityId] = FUEL.[AvailabilityId]
AND TRUNC(ROUTES.[DateTime]) = TRUNC(FUEL.[DateTime])
AND FUEL.[EventTypeId] = @FuelEventTypeId) AS FUELAMOUNT,
/* ROUTE ENDDATETIME */
(SELECT ROUTEENDDATETIME.[DateTime]
FROM {GPS} ROUTEENDDATETIME
WHERE ROUTEID = ROUTEENDDATETIME.[RouteId]
AND ROUTEENDDATETIME.[EventTypeId] = @RouteCompletedEventTypeId
AND ROWNUM = 1) AS ROUTEEND,
/* INITIAL MILEAGE */
(SELECT INITIALMILEAGE
FROM (SELECT MILEAGEBEGIN.[Value] AS INITIALMILEAGE
FROM {GPS} MILEAGEBEGIN
WHERE ROUTES.[AvailabilityId] = MILEAGEBEGIN.[AvailabilityId]
AND TRUNC(ROUTES.[DateTime]) = TRUNC(MILEAGEBEGIN.[DateTime])
AND MILEAGEBEGIN.[EventTypeId] = @MileageEventTypeId
ORDER BY MILEAGEBEGIN.[DateTime] ASC
)
WHERE ROWNUM = 1),
/* FINAL MILEAGE */
(SELECT FINALMILEAGE
FROM (SELECT MILEAGEEND.[Value] AS FINALMILEAGE
FROM {GPS} MILEAGEEND
WHERE ROUTES.[AvailabilityId] = MILEAGEEND.[AvailabilityId]
AND TRUNC(ROUTES.[DateTime]) = TRUNC(MILEAGEEND.[DateTime])
AND MILEAGEEND.[EventTypeId] = @MileageEventTypeId
ORDER BY MILEAGEEND.[DateTime] DESC
)
WHERE ROWNUM = 1)
FROM {GPS} ROUTES
INNER JOIN {Route}
ON ROUTES.[RouteId] = {Route}.[Id]
INNER JOIN {Availability}
ON ROUTES.[AvailabilityID] = {Availability}.[Id]
INNER JOIN {Driver}
ON {Availability}.[DriverId] = {Driver}.[Id]
INNER JOIN {Vehicle}
ON {Availability}.[VehicleId] = {Vehicle}.[Id]
WHERE ROUTES.[EventTypeId] = @RouteStartedEventTypeId