I have a Location database containing Street_Addr_1 and Street_Addr_2 columns. Some of the instances of Street_Addr_2 contain the same information as Street_Addr_1 and then add other information. Here's an example:
Street_Addr_1: "1944 WILLISTON RD" and Street_Addr_2: "1944 WILLISTON RD FLR 1ST STE 3"
What I need to accomplish is remove the redundant information from Street_Addr_2.
I can use "Left(Street_Addr_2, Len(Street_Addr_1) )" to give me the Left_Part_of_Street_Addr_2 and "Trim(Right(Street_Addr_2, Len(Street_Addr_2) - Len(Street_Addr_1)))" to give me the Right_Part_of_Street_Addr_2.
I have the following SQL:
SELECT Street_addr_1, Len_1, Len_2, Street_addr_2,
Left_Part_of_Street_Addr_2, Right_Part_of_Street_Addr_2,
City, State, Country
From
(
SELECT Street_addr_1, len(Street_Addr_1) as Len_1, len(Street_Addr_2) as Len_2,
Street_addr_2, Left(Street_Addr_2, Len(Street_Addr_1) ) as Left_Part_of_Street_Addr_2,
Trim(Right(Street_Addr_2, Len(Street_Addr_2) - Len(Street_Addr_1))) as Right_Part_of_Street_Addr_2,
City, State, Country
FROM Working_Location
where
Street_addr_1 is not null
and Street_addr_2 is not null
and Len(Street_Addr_1) > 0
and Len(Street_Addr_2) > 0
and Len(Street_Addr_1) + 1 < Len(Street_Addr_2)
) as wl1
Where Street_addr_1 = Left_Part_of_Street_Addr_2
Order by State, City, Street_Addr_2
;
The Where clause produces the Data Type Mismatch error. Without that condition, the query works fine and correctly identifies the two parts of Street_Addr_2.
I tried wrapping both sides of the condition with CStr() and that got rid of the Data Type Mismatch error but gave me an Invalid Use of Null error. There should be no null values because the inner select should eliminate them. Next I tried CStr(NZ( )) to replace any nulls with a 0 and then convert it to a string -- Where CStr(NZ(Street_addr_1)) = CStr(NZ(Left_Part_of_Street_Addr_2 )) -- and that brought back the Data Type Mismatch error.
Now, if I add the line "Into Temp1" just above the From line for the outer Select in the query above without using the Where clause for the outer select, it creates Temp1 with data from all rows containing a value for Street_Addr_2.
Next, I can run the following SQL:
SELECT Street_addr_1, Len_1, Len_2, Street_addr_2,
Left_Part_of_Street_Addr_2, Right_Part_of_Street_Addr_2,
City, State, Country
From temp1
Where Street_addr_1 = Left_Part_of_Street_Addr_2
Order by State, City, Street_Addr_2
;
Notice, this is identical to the original SQL replacing the inner select with temp1 as the data source.
Now, I have the follow data "1944 WILLISTON RD" populated into the Left_Part_of_Street_Addr_2 column and "FLR 1ST STE 3" populated into the Right_Part_of_Street_Addr_2.
Question, why does the original query fail but using an intermediate table succeed?
Thank you.