February 26, 2009 at 12:27 pm
We will be converting to 2005 soon. So, I need to replace my *= with LEFT OUTER JOIN. Can someone show me how to convert the following? I've tried about 100 ways and I still get syntax errors. Thank you.
SELECT BL_TRUCK_BOL_TRAILERS.bol_no,
BL_TRUCK_BOL_TRAILERS.rev_no,
BL_TRUCK_BOL_TRAILERS.trailer_id,
BL_TRUCK_BOL_COMMODITY.comm_code,
BL_TRUCK_BOL_DEST_COMM.prod_storage_id,
BL_TRUCK_BOL_COMMODITY.comm_um,
BL_TRUCK_BOL_DEST_COMM_ORDER.quantity,
BL_TRUCK_BOL_COMMODITY.end_scale_wt,
BL_TRUCK_BOL_COMMODITY.quantity,
extended_weight = (select multiplier * BL_TRUCK_BOL_DEST_COMM_ORDER.quantity
from BDS_COMMODITY_UM_CONVERT bcum
where bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and bcum.from_um = BL_TRUCK_BOL_DEST_COMM.comm_um),
um_convert = (select to_um
from BDS_COMMODITY_UM_CONVERT bcum
where bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and bcum.from_um = BL_TRUCK_BOL_DEST_COMM.comm_um)
FROM BL_TRUCK_BOL_TRAILERS,
BL_TRUCK_BOL_COMMODITY,
BL_TRUCK_BOL_DEST,
BL_TRUCK_BOL_DEST_COMM,
BL_TRUCK_BOL_DEST_COMM_ORDER
where BL_TRUCK_BOL_DEST.bol_no = BL_TRUCK_BOL_TRAILERS.bol_no
and BL_TRUCK_BOL_DEST.dest_bol_no *= BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and BL_TRUCK_BOL_DEST.rev_no *= BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no
and BL_TRUCK_BOL_DEST.dest_bol_no = BL_TRUCK_BOL_DEST_COMM.dest_bol_no
and BL_TRUCK_BOL_TRAILERS.bol_no = BL_TRUCK_BOL_COMMODITY.bol_no
and BL_TRUCK_BOL_TRAILERS.rev_no = BL_TRUCK_BOL_COMMODITY.rev_no
and BL_TRUCK_BOL_DEST_COMM.comm_code = BL_TRUCK_BOL_COMMODITY.comm_code
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no *= BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and BL_TRUCK_BOL_DEST_COMM.rev_no *= BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no
and BL_TRUCK_BOL_DEST_COMM.comm_code *= BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code
and BL_TRUCK_BOL_DEST.rev_no = BL_TRUCK_BOL_DEST_COMM.rev_no
and BL_TRUCK_BOL_DEST_COMM.trailer_id = BL_TRUCK_BOL_TRAILERS.trailer_id
and BL_TRUCK_BOL_TRAILERS.trailer_id = BL_TRUCK_BOL_COMMODITY.trailer_id
February 26, 2009 at 12:43 pm
[font="Verdana"]Good luck![/font]
February 26, 2009 at 12:54 pm
Does this work?
It's untested.
SELECT
T.bol_no,
T.rev_no,
T.trailer_id,
C.comm_code,
DC.prod_storage_id,
C.comm_um,
DCO.quantity,
C.end_scale_wt,
C.quantity,
extended_weight = (
SELECT multiplier * DCO.quantity
FROM BDS_COMMODITY_UM_CONVERT BCUM
WHERE (BCUM.comm_code = DC.comm_code)
AND (BCUM.comm_code = DC.comm_code)
AND (DC.dest_bol_no = DCO.dest_bol_no)
AND (BCUM.from_um = DC.comm_um)),
um_convert = (
SELECT to_um
FROM BDS_COMMODITY_UM_CONVERT BCUM
WHERE BCUM.comm_code = DC.comm_code
AND (BCUM.comm_code = DC.comm_code)
AND (DC.dest_bol_no = DCO.dest_bol_no)
AND (BCUM.from_um = DC.comm_um))
FROM BL_TRUCK_BOL_TRAILERS T
INNER JOIN BL_TRUCK_BOL_COMMODITY C
ON (T.bol_no = C.bol_no
AND T.rev_no = C.rev_no
AND T.trailer_id = C.trailer_id)
INNER JOIN BL_TRUCK_BOL_DEST D
ON (T.bol_no = D.bol_no)
INNER JOIN BL_TRUCK_BOL_DEST_COMM DC
ON (D.dest_bol_no = DC.dest_bol_no
AND D.rev_no = DC.rev_no
AND T.trailer_id = DC.trailer_id
AND C.comm_code = DC.comm_code)
LEFT OUTER JOIN BL_TRUCK_BOL_DEST_COMM_ORDER DCO
ON (D.dest_bol_no = DCO.dest_bol_no
AND D.rev_no = DCO.rev_no
AND DC.dest_bol_no = DCO.dest_bol_no
AND DC.rev_no = DCO.rev_no
AND DC.comm_code = DCO.comm_code)
February 26, 2009 at 12:58 pm
I think this will work. Can't test it, but I think it will.
SELECT
BL_TRUCK_BOL_TRAILERS.bol_no,
BL_TRUCK_BOL_TRAILERS.rev_no,
BL_TRUCK_BOL_TRAILERS.trailer_id,
BL_TRUCK_BOL_COMMODITY.comm_code,
BL_TRUCK_BOL_DEST_COMM.prod_storage_id,
BL_TRUCK_BOL_COMMODITY.comm_um,
BL_TRUCK_BOL_DEST_COMM_ORDER.quantity,
BL_TRUCK_BOL_COMMODITY.end_scale_wt,
BL_TRUCK_BOL_COMMODITY.quantity,
(select multiplier * BL_TRUCK_BOL_DEST_COMM_ORDER.quantity
from BDS_COMMODITY_UM_CONVERT bcum
where bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and bcum.from_um = BL_TRUCK_BOL_DEST_COMM.comm_um) as extended_weight,
(select to_um
from BDS_COMMODITY_UM_CONVERT bcum
where bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and bcum.comm_code = BL_TRUCK_BOL_DEST_COMM.comm_code
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and bcum.from_um = BL_TRUCK_BOL_DEST_COMM.comm_um) as um_convert
FROM
BL_TRUCK_BOL_TRAILERS,
inner join BL_TRUCK_BOL_COMMODITY
on BL_TRUCK_BOL_TRAILERS.bol_no = BL_TRUCK_BOL_COMMODITY.bol_no
and BL_TRUCK_BOL_TRAILERS.rev_no = BL_TRUCK_BOL_COMMODITY.rev_no
and BL_TRUCK_BOL_TRAILERS.trailer_id = BL_TRUCK_BOL_COMMODITY.trailer_id
inner join BL_TRUCK_BOL_DEST
on BL_TRUCK_BOL_DEST.bol_no = BL_TRUCK_BOL_TRAILERS.bol_no
and BL_TRUCK_BOL_DEST_COMM.comm_code = BL_TRUCK_BOL_COMMODITY.comm_code
left outer join BL_TRUCK_BOL_DEST_COMM
on BL_TRUCK_BOL_DEST.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and BL_TRUCK_BOL_DEST.dest_bol_no = BL_TRUCK_BOL_DEST_COMM.dest_bol_no
and BL_TRUCK_BOL_DEST.rev_no = BL_TRUCK_BOL_DEST_COMM.rev_no
and BL_TRUCK_BOL_DEST_COMM.trailer_id = BL_TRUCK_BOL_TRAILERS.trailer_id
left outer join BL_TRUCK_BOL_DEST_COMM_ORDER
on BL_TRUCK_BOL_DEST.rev_no = BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no
and BL_TRUCK_BOL_DEST_COMM.dest_bol_no = BL_TRUCK_BOL_DEST_COMM_ORDER.dest_bol_no
and BL_TRUCK_BOL_DEST_COMM.rev_no = BL_TRUCK_BOL_DEST_COMM_ORDER.rev_no
and BL_TRUCK_BOL_DEST_COMM.comm_code = BL_TRUCK_BOL_DEST_COMM_ORDER.comm_code;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 1:02 pm
This works great and I can use it as a template going forward. Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply