Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Question Unanswered: Outer nested non-ANSI joins (aaaarrrrggg)

    I've tried everything and I just can't convert this to an ANSI join format and have it work.

    SELECT
    FIELD LIST
    FROM
    order o,
    OUTER(tour t1, v_order_to_job_ids vo1, tour_point tp1, activity act1, OUTER activity_aux aa),
    OUTER(tour t2, v_order_to_job_ids vo2, tour_point tp2, activity act2),
    OUTER order_aux oa,
    OUTER (
    SELECT order_id,
    SUM(percent_of_trailer) *100 AS percentOfTrailer,
    first_delivery
    FROM order_detail
    WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND status_cid NOT IN(1214, 1215)
    /* Inactive, Delete */

    GROUP BY order_id, first_delivery) pot
    WHERE act1.status_cid != 1001
    AND act1.tour_point_id = tp1.id
    AND act1.sub_type_cid != 1367
    AND tp1.tour_id = t1.id
    AND tp1.seq_num = 1
    AND vo1.o_id = o.id
    AND vo1.t_id = t1.id
    AND act2.status_cid != 1001
    AND act2.tour_point_id = tp2.id
    AND act2.sub_type_cid != 1367
    AND tp2.tour_id = t2.id
    AND tp2.seq_num =(
    SELECT max(seq_num)
    FROM tour_point
    WHERE tour_id = t2.id
    AND status_cid != 1311)
    AND vo2.o_id = o.id
    AND vo2.t_id = t2.id
    AND o.id = oa.order_id
    AND o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND o.cust_role_id = 64
    AND aa.activity_id = act1.id
    AND aa.active = 1
    AND o.id = pot.order_id


    Here is how I THOUGHT it should look but it does not work . . .

    SELECT
    FIELD LIST
    FROM
    order o
    LEFT OUTER JOIN v_order_to_job_ids vo1
    ON vo1.o_id = o.id
    INNER JOIN tour t1
    ON vo1.t_id = t1.id
    INNER JOIN tour_point tp1
    ON (t1.id = tp1.tour_id and tp1.seq_num = 1)
    INNER JOIN activity act1
    ON (act1.tour_point_id = tp1.id AND act1.sub_type_cid != 1367 AND act1.status_cid != 1001)
    LEFT OUTER JOIN activity_aux aa
    ON (act1.id = aa.activity_id AND aa.active = 1)
    LEFT OUTER JOIN v_order_to_job_ids vo2
    ON o.id = vo2.o_id
    INNER JOIN tour t2
    ON vo2.t_id = t2.id
    INNER JOIN tour_point tp2
    ON (tp2.tour_id = t2.id AND tp2.seq_num =
    (
    SELECT max(seq_num)
    FROM tour_point
    WHERE tour_id = t2.id
    AND status_cid != 1311))
    INNER JOIN activity act2
    ON (act2.tour_point_id = tp2.id AND act2.status_cid != 1001 AND act2.sub_type_cid != 1367)
    LEFT OUTER JOIN order_aux oa
    ON o.id = oa.order_id
    LEFT OUTER JOIN
    (
    SELECT order_id,
    SUM(percent_of_trailer) *100 AS percentOfTrailer,
    first_delivery
    FROM order_detail
    WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND status_cid NOT IN(1214, 1215)
    /* Inactive, Delete */

    GROUP BY order_id, first_delivery
    ) pot
    ON o.id = pot.order_id
    WHERE
    o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND o.cust_role_id = 64

  2. #2
    Join Date
    Apr 2010
    Posts
    5

    Even this does not work.

    The query just runs forever . . .

    SELECT
    *
    FROM
    order o
    left outer join
    (tour t1
    inner join v_order_to_job_ids vo1
    ON vo1.t_id = t1.id
    inner join tour_point tp1
    ON tp1.tour_id = t1.id and tp1.seq_num = 1
    inner join activity act1
    ON act1.tour_point_id = tp1.id and act1.status_cid != 1001 and act1.sub_type_cid != 1367
    left OUTER join activity_aux aa
    ON act1.id = aa.activity_id and aa.active = 1)
    ON o.id = vo1.o_id
    left OUTER join
    (tour t2
    inner join v_order_to_job_ids vo2
    ON vo2.t_id = t2.id
    inner join tour_point tp2
    ON (tp2.tour_id = t2.id AND tp2.seq_num =
    (
    SELECT max(seq_num)
    FROM tour_point
    WHERE tour_id = t2.id
    AND status_cid != 1311
    )
    )
    inner join activity act2
    ON act2.tour_point_id = tp2.id AND act2.status_cid != 1001 AND act2.sub_type_cid != 1367)
    ON o.id = vo2.o_id
    left OUTER join order_aux oa
    ON o.id = oa.order_id
    left OUTER join (
    SELECT order_id,
    SUM(percent_of_trailer) *100 AS percentOfTrailer,
    first_delivery
    FROM order_detail
    WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND status_cid NOT IN(1214, 1215)
    /* Inactive, Delete */

    GROUP BY order_id, first_delivery) pot
    ON o.id = pot.order_id
    WHERE
    o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
    AND o.cust_role_id = 64

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •