I'm doing the select below but in the last subselect (mileage) I'm having problems with the ORDER BY error "ORA-00907: missing right parenthesis", if I remove ORDER BY it works.
SELECT /* DATE OF THE ROUTE */
{GPS}.[DateTime],
/* 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] = {GPS}.[RouteId]) AS StopCount,
/* AMOUNT OF FUEL */
(SELECT SUM(FUEL.[Value])
FROM {GPS} FUEL
WHERE {GPS}.[RouteId] = FUEL.[RouteId]
AND FUEL.[EventTypeId] = 23) FuelAmount, /* Event Fuel */
/* ROUTE STARTDATETIME */
{GPS}.[DateTime] AS ROUTESTARTDATETIME,
/* ROUTE ENDDATETIME */
(SELECT ROUTEENDDATETIME.[DateTime]
FROM {GPS} ROUTEENDDATETIME
WHERE {GPS}.[RouteId] = ROUTEENDDATETIME.[RouteId]
AND ROUTEENDDATETIME.[EventTypeId] = 5 /* Event Route Completed */
AND ROWNUM = 1) AS ROUTEEND,
/* INITIAL MILEAGE */
(SELECT MILEAGEBEGIN.[Value]
FROM {GPS} MILEAGEBEGIN
WHERE {GPS}.[RouteId] = MILEAGEBEGIN.[RouteId]
AND MILEAGEBEGIN.[EventTypeId] = 21 /* Event Mileage */
AND ROWNUM = 1
ORDER BY MILEAGEBEGIN.[DateTime]
) AS INITIALMILEAGE
FROM {GPS}
INNER JOIN {Route}
ON {GPS}.[RouteId] = {Route}.[Id]
INNER JOIN {Driver}
ON {GPS}.[DriverId] = {Driver}.[Id]
INNER JOIN {Availability}
ON {Driver}.[Id] = {Availability}.[DriverId]
INNER JOIN {Vehicle}
ON {Availability}.[VehicleId] = {Vehicle}.[Id]
WHERE {GPS}.[EventTypeId] = 3 /* Event RouteStarted */
I have tried to do the following but the error: "ORA-00936: missing expression".
SELECT /* DATE OF THE ROUTE */
{GPS}.[DateTime],
/* INITIAL MILEAGE */
SELECT TEST,'more test' FROM (SELECT MILEAGEBEGIN.[Value] AS TEST
FROM {GPS} MILEAGEBEGIN
WHERE {GPS}.[RouteId] = MILEAGEBEGIN.[RouteId]
AND MILEAGEBEGIN.[EventTypeId] = 21 /* Event Mileage */
ORDER BY MILEAGEBEGIN.[DateTime] ASC
)
WHERE ROWNUM = 1 AS INITIALMILEAGE
FROM {GPS}
WHERE {GPS}.[EventTypeId] = 3 /* Event RouteStarted */
/ ********************* CORRECTED QUERY *********************** ********* /
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