Postgresql – Creating two sub queries using WITH clause

postgresql

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?

Best Answer

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.

Related Question