February 23, 2009 at 9:26 am
Philipp Post (2/20/2009)
Am I incorrect in this thinking? or Is it better to pass the enitre table. I figured that passing the field would be more efficient than passing the entire table.
The most efficent would be to fix the address table one time and then the queries would be easy. The table is not designed correctly and you will run into problems over and over again.
I agree. The vendor is is supposed to fix this in the future but for now I have to deal with it as is... It is a single field in the application.
Jack - I think I finally get what you were trying to do/say:
so below is the modified code. legal_entity_sk is my primary/unique id. I had to change address to address_T wouldn't let me use it. Seems to be a stored procedure or something. Haven't got a response from vendor what that is... Unless I am missing something that you trying to do.
DECLARE @new_addresses TABLE (address_Line_id INT, legal_entity_sk INT, address_T VARCHAR(255))
/* this cte creates a "temporary numbers view" for use in splitting the addresses
see this article http://www.sqlservercentral.com/articles/TSQL/62867/ for deeper
explanation of all this code except the cte*/
;WITH cteNums AS
(
SELECT TOP 100000
ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS n
FROM
sys.all_columns AS AC
)
-- insert the split addresses into the intermediate table
INSERT INTO @new_addresses (
address_Line_id,
legal_entity_sk,
address_T
) SELECT
-- this identifies which one comes first
ROW_NUMBER() OVER (ORDER BY legal_entity_sk, n) AS row_id,
legal_entity_sk, -- to join on original table
-- splits the long address. You need to pre-pend and append the split character for this to work
SUBSTRING(CHAR(10) + address_1 + CHAR(10), n+1,
CHARINDEX(CHAR(10), CHAR(10) + address_1 + CHAR(10), n + 1) - n - 1) AS address_T
FROM
-- the cross join provides a row for each row in each table
vlegal_entity CROSS JOIN
cteNums
WHERE
-- this gets the number we need
n < LEN(CHAR(10) + address_1 + CHAR(10)) AND
-- this finds the next split character again make sure you pre-pend and append it
SUBSTRING(CHAR(10) + address_1 + CHAR(10), n, 1) = CHAR(10)
ORDER BY n
/* now we can return the data and assumes we always have 1 address row and up to 3 */
SELECT
A.legal_entity_sk,
NA1.address_T AS address_1,
NA2.address_T AS address_2,
NA3.address_T AS address_3
FROM
vlegal_entity AS A JOIN
@new_addresses AS NA1 ON
A.legal_entity_sk = NA1.legal_entity_sk AND
NA1.address_Line_id = 1 LEFT JOIN
@new_addresses AS NA2 ON
A.legal_entity_sk = NA2.legal_entity_sk AND
NA2.address_Line_id = 2LEFT JOIN
@new_addresses AS NA3 ON
A.legal_entity_sk = NA3.legal_entity_sk AND
NA3.address_Line_id = 3
Now the issue I have run into is I get an error:
Msg 402, Level 16, State 1, Line 6
The data types char and text are incompatible in the add operator.
This I believe (correct me if I am wrong) is due to the address_1.vlegal_entity is TEXT vs the address_T being a VARCHAR.... so If this is correct, how would you recommend working with this?
February 23, 2009 at 9:46 am
Paul Morris (2/23/2009)
Now the issue I have run into is I get an error:Msg 402, Level 16, State 1, Line 6
The data types char and text are incompatible in the add operator.
This I believe (correct me if I am wrong) is due to the address_1.vlegal_entity is TEXT vs the address_T being a VARCHAR.... so If this is correct, how would you recommend working with this?
Right. You cannot use the concatentation operator (+) with the TEXT or NTEXT data type. I would recommend, since you are using SQL Server 2005, doing a Select [columns] Into #temp_addresses From vlegal_entity where you would convert the Address_1 Column from TEXT to varchar(max) and I would also do the prepending and appending of CHAR(10) at this time as well.
Is vlegal_entity a view? If it is, what do you see when you run sp_helptext 'vlegal_entity'? It may be that the vendor created this view and concatenated multiple address columns into address_1 with the carriage returns/line feed added and you could use the base tables for the report.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2009 at 9:53 am
It is a view but the base table is the same for the address_1 column. The view simplfies some joins I need to return the entire address (i.e. name of company, etc...).
Let me play with creating the conversion part of this....
Thanks for your help so far!
February 24, 2009 at 7:00 am
OK so far I am not doing to bad... couldn't get the syntax correct for convert so used cast instead. So I modified this section based on your recommendations:
SELECT
-- this identifies which one comes first
ROW_NUMBER() OVER (ORDER BY legal_entity_sk, n) AS row_id,
legal_entity_sk, -- to join on original table
-- splits the long address. You need to pre-pend and append the split character for this to work
SUBSTRING(CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10), n+1,
CHARINDEX(CHAR(10), CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10), n + 1) - n - 1) AS address_T
FROM
-- the cross join provides a row for each row in each table
vlegal_entity CROSS JOIN
cteNums
WHERE
-- this gets the number we need
n < LEN(CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10)) AND
-- this finds the next split character again make sure you pre-pend and append it
SUBSTRING(CHAR(10) + (cast(address_1 AS varchar(max)))+ CHAR(10), n, 1) = CHAR(10) ORDER BY n
Now if the above is correct.... I did run it the whole thing but it is only returning a single row... Is there a way for me to step through this to see what is happening?
February 24, 2009 at 2:09 pm
I don't see anything out of the ordinary that would cause only 1 row to be returned. I think the only reason it would return 1 row would be if there are no CHAR(10)'s (Line Feed) in the column. Maybe they are using CHAR(13) (Carriage Return).
Do a charindex(Char(10), address_1) and a CharIndex(Char(13), address_1) and see which one is found.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 24, 2009 at 2:15 pm
Jack Corbett (2/24/2009)
I don't see anything out of the ordinary that would cause only 1 row to be returned. I think the only reason it would return 1 row would be if there are no CHAR(10)'s (Line Feed) in the column. Maybe they are using CHAR(13) (Carriage Return).Do a charindex(Char(10), address_1) and a CharIndex(Char(13), address_1) and see which one is found.
Disregard the last post, I'm an idiot. You need to add a Partition By to the Row_Number Function. The row_id line should be like this:
ROW_NUMBER() OVER (PARTITION BY address_Id ORDER BY address_id, n) AS row_id
Without the partition by it continues to grow in number so there is only 1 address that has a row_id of 1, 2, 3. The Partition By says reset the row_number whenever the partition changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 7:09 am
WOOOHOOOO!!!! That got it!!! THANKS JACK FOR YOUR HELP AND PATIENCES IN THIS...
I apologize for being a block head... this stuff is a bit outside my skill set. 😀
For those interested, here is the final code:
DECLARE @new_addresses TABLE (address_Line_id INT, legal_entity_sk INT, address_T VARCHAR(255))
/* this cte creates a "temporary numbers view" for use in splitting the addressessee
this article http://www.sqlservercentral.com/articles/TSQL/62867/ for
deeperexplanation of all this code except the cte*/
;WITH cteNums AS
(
SELECT TOP 100000
-- PARTITIION BY resets the row_number whenever the partition changes
ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS n
FROM
sys.all_columns AS AC
)
-- insert the split addresses into the intermediate table
INSERT INTO @new_addresses (
address_Line_id,
legal_entity_sk,
address_T
) SELECT
-- this identifies which one comes first. PARTITIION BY resets the row_number whenever the partition changes
ROW_NUMBER() OVER (PARTITION BY legal_entity_sk ORDER BY legal_entity_sk, n) AS row_id, legal_entity_sk,
-- splits the long address. You need to pre-pend and append the split character for this to work
SUBSTRING(CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10), n+1,
CHARINDEX(CHAR(10), CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10), n + 1) - n - 1) AS address_T
FROM
-- the cross join provides a row for each row in each table
vlegal_entity CROSS JOIN
cteNums
WHERE
-- this gets the number we need
n < LEN(CHAR(10) + (cast(address_1 AS varchar(max))) + CHAR(10)) AND
-- this finds the next split character again make sure you pre-pend and append it
SUBSTRING(CHAR(10) + (cast(address_1 AS varchar(max)))+ CHAR(10), n, 1) = CHAR(10) ORDER BY n
/* now we can return the data and assumes we always have 1 address row and up to 3 */
SELECT
A.legal_entity_sk,
NA1.address_T AS address_1,
NA2.address_T AS address_2,
NA3.address_T AS address_3
FROM
vlegal_entity AS A JOIN
@new_addresses AS NA1 ON
A.legal_entity_sk = NA1.legal_entity_sk AND
NA1.address_Line_id = 1 LEFT JOIN
@new_addresses AS NA2 ON
A.legal_entity_sk = NA2.legal_entity_sk AND
NA2.address_Line_id = 2LEFT JOIN
@new_addresses AS NA3 ON
A.legal_entity_sk = NA3.legal_entity_sk AND
NA3.address_Line_id = 3
Now for the hard part...;) Learning how to put this on DB and call it in the reports...
Thanks again for all your help!!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply