How to assign aliases to multiple select statements

Question

My issue is the right join is not working, I am guessing due to aliases used.

I have commented the lines using -- to indicate the lines that most probably are causing the issue. If I run my Cross Join part of the code separately it works fine.

If I add the cross join as show below, and then do a right join with the table final_leg a I am receiving an error saying 'relation "k" does not exist'.

k is the alias I am using in the last select statement to call the CROSS join part of the table.

   WITH final_leg AS(
    SELECT y.* 
    FROM 
           (
           SELECT
                 y.shipment_id,
                 y.route_id,
                 max(leg_sequence_id) max_leg_sequence_id
           FROM posimorders.sc_execution_eu.o_detailed_routes_v2 y
           group by
                 1,2
           ) AS x
           INNER JOIN posimorders.sc_execution_eu.o_detailed_routes_v2 y
           on x.route_id = y.route_id and x.shipment_id = y.shipment_id and y.leg_sequence_id = x.max_leg_sequence_id
    ),

    dest_leg AS(
    SELECT y.* 
    FROM 
           (
           SELECT
                 y.shipment_id,
                 y.route_id,
                 min(leg_sequence_id) max_leg_sequence_id
           FROM 
                 posimorders.sc_execution_eu.o_detailed_routes_v2 y
                 LEFT JOIN warehouse_attributes w                  -- Joining to add origin country of origin FC
                 ON w.warehouse_id = y.leg_warehouse_id

           group by
                 1,2
           ) x
           INNER JOIN posimorders.sc_execution_eu.o_detailed_routes_v2 y
           on x.route_id = y.route_id and x.shipment_id = y.shipment_id and y.leg_sequence_id = x.max_leg_sequence_id
    ),

list_legs_ds AS(
SELECT t1.*, t2.* FROM

(SELECT leg_warehouse_id, SUM(pck_count) AS total_packages
FROM posimorders.sc_execution_eu.o_detailed_routes_v2
WHERE trunc(cpt_date) between '2019-06-16' and '2019-06-22'
    and leg_sequence_id = 0
    and leg_warehouse_id not like 'X%'
    and right(leg_warehouse_id,1) in ('1','2','3','4','5','6','7','8','9')    --Only SC and not Airports
group by 1
having sum(pck_count) > 50000
) t1

CROSS JOIN

(select distinct leg_warehouse_id AS lm_ds , destination_country_code
from posimorders.sc_execution_eu.o_detailed_routes_v2
where trunc(cpt_date) BETWEEN '2019-06-16' and '2019-06-22'
    and leg_ship_method LIKE 'AMZN_%_PRIME'
    ) t2
    )

    SELECT
           a.route_warehouse_id,
           --k.leg_warehouse_id leg_ware,
           --k.leg_warehouse_id lm_ds,
    from
           final_leg a 
           inner join dest_leg b 
           on a.shipment_id = b.shipment_id and a.route_id = b.route_id

           --RIGHT JOIN list_legs_ds k
           --on a.leg_warehouse_id = k.leg_ware -- and a.leg_ship_method = k.last_ds

Answer 1

The issue is most likely because you haven't aliased the columns in your list_legs_ds subquery, meaning you have at least two columns with the same name.

This is a good example of why you shouldn't, in general, use select *... and why you should be explicit in the columns you're selecting. Try the following instead:

WITH final_leg AS
 (SELECT y.*
  FROM   (SELECT y.shipment_id,
                 y.route_id,
                 MAX(leg_sequence_id) max_leg_sequence_id
          FROM   posimorders.sc_execution_eu.o_detailed_routes_v2 y
          GROUP  BY 1,
                    2) AS x
  INNER  JOIN posimorders.sc_execution_eu.o_detailed_routes_v2 y
  ON     x.route_id = y.route_id
  AND    x.shipment_id = y.shipment_id
  AND    y.leg_sequence_id = x.max_leg_sequence_id),
dest_leg AS
 (SELECT y.*
  FROM   (SELECT y.shipment_id,
                 y.route_id,
                 MIN(leg_sequence_id) max_leg_sequence_id
          FROM   posimorders.sc_execution_eu.o_detailed_routes_v2 y
          LEFT   JOIN warehouse_attributes w -- Joining to add origin country of origin FC
          ON     w.warehouse_id = y.leg_warehouse_id

          GROUP  BY 1,
                    2) x
  INNER  JOIN posimorders.sc_execution_eu.o_detailed_routes_v2 y
  ON     x.route_id = y.route_id
  AND    x.shipment_id = y.shipment_id
  AND    y.leg_sequence_id = x.max_leg_sequence_id),
list_legs_ds AS
 (SELECT t1.leg_warehouse_id         AS leg_ware,
         t1.total_packages,
         t2.leg_warehouse_id         AS last_ds,
         t2.destination_country_code
  FROM   (SELECT leg_warehouse_id AS leg_ware,
                 SUM(pck_count) AS total_packages
          FROM   posimorders.sc_execution_eu.o_detailed_routes_v2
          GROUP  BY 1
          HAVING SUM(pck_count) > 50000) t1
  CROSS  JOIN (SELECT DISTINCT leg_warehouse_id AS last_ds,
                               destination_country_code
               FROM   posimorders.sc_execution_eu.o_detailed_routes_v2) t2)
SELECT a.route_warehouse_id,
       k.leg_ware,
       k.last_ds lm_ds
       -- should there be something to aggregate here?
FROM   final_leg a
INNER  JOIN dest_leg b
ON     a.shipment_id = b.shipment_id
AND    a.route_id = b.route_id
RIGHT  JOIN list_legs_ds k
ON     a.leg_warehouse_id = k.leg_ware -- and a.leg_ship_method = k.lm_ds
GROUP  BY 1,
          2,
          3;