How to match records on several substring functions

[Rnt6872]

Hello All,
I have a sql statement that I'm using to match records on. But i need
to use a substring to get the nuber that I need, and I have to use
several substrings to get the number since its embedded in other text.
Here's my sql:
use fto_ups
select distinct o.Shipper#, o.InvoiceDate, o.InvoiceNumber, o.ChgType,
o.Lading, o.BillTo, o.Name2, o.Address1, o.Address2, o.City, o.State,
o.Zip,
o.ShipFromName1, o.ShipFromeName2, o.ShipFromAddress1,
o.ShipFromAddess2, o.ShipFromCity, o.ShipFromState, o.ShipFromZip,
o.PickupDate,o.PickUpRecord, o.TrackingNumber,
o.BOL, o.Reference2, o.ServiceType, o.ServiceZip, o.Zone, o.Weight,
o.PublishedCharge, o.IncentiveCredit, o.InsuredCharge, o.BilledCharge,
o.DelAreaSurcharge,
m.FRRDSSTrackingNbr,m.FRRDSSShipmentNbr, m.FRRDSSDivision,
m.FRRDSSDeptNbr, m.FRRDSSVendorNbr, m.FRRDSSChargeBackNbr,
m.FRRDSSShipmentCreateDate,m.[UPS Units],m.[UPS % Units to Total]
into dbo.tempOutBoundTable
from upsOutbound o left join msoutboundhistory m on right(o.bol,7) =
m.FRRDSSShipmentNbr and o.bol = m.FRRDSSShipmentNbr
********
How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?

[Roy Harvey]

On 13 Dec 2006 09:47:01 -0800, "Rnt6872" <r_******@msn.comwrote:

>How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?

I don't know just what you are asking. The existing query already
uses a substring - well, RIGHT() in this case, but the same idea - to
perform the LEFT OUTER JOIN.

FROM upsOutbound o
LEFT OUTER
JOIN msoutboundhistory m
ON right(o.bol,7) = m.FRRDSSShipmentNbr
AND o.bol = m.FRRDSSShipmentNbr

If you have another test for substring(o.bol,4,7) you should be able
to use the same approach but with substring() instead of right().

Roy Harvey
Beacon Falls, CT

[Rnt6872]

Thank you. I'll try that.
Roy Harvey wrote:

On 13 Dec 2006 09:47:01 -0800, "Rnt6872" <r_******@msn.comwrote:

How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?

I don't know just what you are asking. The existing query already
uses a substring - well, RIGHT() in this case, but the same idea - to
perform the LEFT OUTER JOIN.

FROM upsOutbound o
LEFT OUTER
JOIN msoutboundhistory m
ON right(o.bol,7) = m.FRRDSSShipmentNbr
AND o.bol = m.FRRDSSShipmentNbr

If you have another test for substring(o.bol,4,7) you should be able
to use the same approach but with substring() instead of right().

Roy Harvey
Beacon Falls, CT

[Matik]

... if I may add:
Try avoid using joins on substrings. This can harm your performance in
the future, while the server can no operate with indexes. That means,
even you will have an index on that filed, it will be not used, afaik.

Best regards

Matik
Rnt6872 napisal(a):

Thank you. I'll try that.