SELECT Invalid identifier

1

I'm having problems where I use {Routes}.[Qualquer] in subselects . The error returned is

  

"ROUTES". "COLUMN": invalid identifier.

I tried to expose the column in select but continue with the same error.

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 
    
asked by anonymous 24.05.2018 / 13:14

0 answers