Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Variable field length join Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 4:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 146, Visits: 791
Hi,

I have a join consisting of two tables (VCTMovements and VCTHaulageMileageLog).

SELECT * FROM VCTMovements vm JOIN VCTHaulageMileageLog vhml ON
vm.VCTCollectZipcodeId = vhml.ZipcodeFrom
AND vm.VCTDeliveryZipcodeId = vhml.ZipcodeTo
AND vm.DataAreaId = vhml.DataAreaId
WHERE vm.VCTCollectZipcodeId IS NOT NULL
AND vm.VCTDeliveryZipcodeId IS NOT NULL
AND vm.VCTCollectZipcodeId <> ''
AND vm.VCTDeliveryZipcodeId <> ''
AND vhml.MatrixType = 2

Ordinarily the above code would be fine. However, there are some records in the VCTHaulageMileageLog table where partial data was entred in the ZipcodeFrom and ZipcodeTo fields.
These fields are both NVARCHAR(10).

I could exeucte a lengthy statement to catch each permutation (i.e. where length of ZipcodeFrom = 1 and length of ZipcodeTo = 1), but is there a better approach than the following example?

SELECT * FROM VCTMovements vm JOIN VCTHaulageMileageLog vhml ON
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 1) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 1) = vhml.ZipcodeTo
)
OR
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 2) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 1) = vhml.ZipcodeTo
)
... etc
... etc
OR
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 10) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 10) = vhml.ZipcodeTo
)
WHERE vm.VCTCollectZipcodeId IS NOT NULL
AND vm.VCTDeliveryZipcodeId IS NOT NULL
AND vm.VCTCollectZipcodeId <> ''
AND vm.VCTDeliveryZipcodeId <> ''
AND vhml.MatrixType = 2

Any ideas, please?

Thanks in advance,

Neal
Post #1067016
Posted Monday, February 21, 2011 4:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 834, Visits: 5,322
-- either (LEN ignores trailing spaces)
LEFT(vm.VCTCollectZipcodeId, LEN(vhml.ZipcodeFrom)) = vhml.ZipcodeFrom
-- or
vm.VCTCollectZipcodeId LIKE vhml.ZipcodeFrom + '%'

Post #1067030
Posted Monday, February 21, 2011 5:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 146, Visits: 791
Many thanks Ken
Post #1067034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse