Can someone please help! I'm having an issue with a query. It's using a SUBSTRING but I think there's a problem with it. It returns 0 rows when it should return 1. Here is the query in question.
SELECT v.veh_id, vi.inventory_id, SUBSTRING(bd.buft_string_1, 35, 70), bd.*
FROM buft_chrysler_data AS bd
INNER JOIN vehicle AS v WITH (NOLOCK)
ON SUBSTRING(bd.buft_string_1, 14, 17) = v.vin
INNER JOIN vehicle_inventory AS vi WITH (NOLOCK)
ON v.veh_id = vi.veh_id
AND vi.own_dtm = (SELECT MAX(o.own_dtm)
FROM ownership AS o WITH (NOLOCK)
WHERE o.veh_id = v.veh_id
AND o.tran_account_id = 1) -- Chrysler
INNER JOIN message_dcx_met_part_audit AS mdmpa WITH (NOLOCK)
--ON LTRIM(RTRIM(SUBSTRING(bd.buft_string_1, 35, 70))) = LTRIM(RTRIM(mdmpa.message_text))
ON SUBSTRING(bd.buft_string_1, 35, 70) = mdmpa.message_text
WHERE bd.tran_event_id = 62139
I think the issue is on "SUBSTRING(bd.buft_string_1, 35, 70) = mdmpa.message_text". The mdmpa.message_text field is a varchar(70), this is why we're using 70 as our Substring length. However, it doesn't seem to like this. It should match where message_text is equal to "ALL MET ITEMS FOR VIN PROCESSED SUCCESSFULLY". If I simply use "SUBSTRING(bd.buft_string_1, 35, 44) instead where 44 is the exact length of the string it works just fine. How do I account for a varchar field though?
Any help would be greatly appreciated!