I'm using the inline querier to execute a sql statement but only if a certain condition is met (defined by tester).
I created my sql statement in MS Access and there it's working fine but when I use the same statement in the inline querier I get following error message:
ERROR |InlineQuerier_3(InlineQueryFactory): Error preparing database query: no such column: stop_times.trip_id Unable to execute statement
ERROR |InlineQuerier_3(InlineQueryFactory): Database was unable to prepare query 'SELECT *
FROM (route_max INNER JOIN (stop_times INNER JOIN stops ON stop_times.stop_id = stops.stop_id) ON route_max.trip_id = stop_times.trip_id) INNER JOIN (routes INNER JOIN Trips ON routes.route_id = Trips.route_id) ON route_max.trip_id = Trips.trip_id;
ERROR |InlineQueryFactory instance was not properly defined. Consult logfile for details.
This is the SQL statement I use:
SELECT *
FROM (route_max INNER JOIN (stop_times INNER JOIN stops ON stop_times.stop_id = stops.stop_id) ON route_max.trip_id = stop_times.trip_id) INNER JOIN (routes INNER JOIN Trips ON routes.route_id = Trips.route_id) ON route_max.trip_id = Trips.trip_id;
For information about tables and columns see attached screenshot
What should I change in order to this to work?
My SQL knowledge isn't that great so what I do in the workbench is:
Run a first inline querier (this one succeeds with 4 outputs)
1 output = route_max (SELECT trip_id, route_id, Max(CountOfstop_id) AS MaxCountStops
FROM (
SELECT Stop_times.trip_id, Trips.route_id, Count(Stop_times.stop_id) AS CountOfstop_id
FROM Trips INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id
GROUP BY Stop_times.trip_id, Trips.route_id
ORDER BY Count(Stop_times.stop_id) DESC)
GROUP BY route_id); I connect with tester and via tester to new inline querier.
Regards
Tom
P.S. I'm using FME 2014 (SP4)
