0

I am struggling with really complex queries involving two sub-queries using WITH clause as described here.

But it appears to me I cannot use one sub-query inside my main query. I here is my query statement:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id, 
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acc
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);

Error:

ERROR:  syntax error at or near "trip_mode"
LINE 53:   trip_mode.travel_mode

I cannot use travel_mode or trip_mode.travel_mode from second sub-query trip_mode in main query. But works on commenting out travel_mode/trip_mode.travel_mode. Where do I go wrong?

1
  • 1
    I'll bet the error is in this line trip_mode.travel_mode
    – McNets
    Oct 8, 2019 at 19:47

2 Answers 2

2

There are two problems with your query:

  1. You are missing a comma before trip_mode.travel_mode.

  2. If you want to reference the CTEs in the main query, you have to add them to the FROM clause.

2
  • Yes, couldn't figure out there's missing comma. Add them to FROM using join?
    – arilwan
    Oct 8, 2019 at 22:05
  • Yes. I didn't look how exactly they should be joined, as I don't know the semantics of your query. Oct 9, 2019 at 4:02
-1

You need to use parenthesis around the case statement.

Try this:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         (CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END) AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id,
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acca,
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.