December 3, 2012 at 10:43 am
I have a need to combine data from multiple rows into one row. The original query ends just before the recursion I attempted to add and lists the data as follows:
'CID' 'AID' SAID , name, 'hp', 'spanish', 'timezone'
10 20 30 John Doe 8005551212 0 EZT
10 20 30 John Doe 8665551212 0 EZT
What I need is:
'CID' 'AID' SAID , name, 'hp', 'spanish', wp, 'timezone'
10 20 30 John Doe 8005551212 0 8665551212 EZT
Below is my query. When I attempt to run it as is now, for each field in the anchor select statement I get: Types don't match between the anchor and the recursive part in column "Row" of recursive query "TempTable1". How can I get around this? I have tried casting the fields with no joy. I'm also open if there is a better way to do this.
Select DISTINCT cat.CID as 'CID',
a.AID as 'AID',
a.SAID as 'SAID',
IsNull(p.FirstName, ' ')+ ' ' + IsNull(p.LastName, ' ') as 'name',
pnh.areacode + pnh.Exchange + pnh.pnumber as 'hp',
isnull (a.Spanish,0) as 'spanish',
tz.TimeZone as 'timezone'
Into #ACTemp
From dbo. Account a (nolock)
INNER JOIN dbo.CACCType cat (nolock)
ON a.SCAID = cat.SCAID
INNER JOIN dbo.AccountAddress aa (nolock)
ON a.SAID = aa.SAID
LEFT OUTER JOIN dbo.Address ad (nolock)
ON aa.SysAddressID = ad.SysAddressID
INNER JOIN dbo.Contact ct (nolock)
ON a.SAID = ct.ContactorID
And ct.SysContactorTypeID = 4
INNER JOIN dbo.Person p (nolock)
ON ct.SysPersonID = p.SysPersonID
INNER JOIN dbo.Client c (nolock)
ON cat.CID = c.CID
LEFT OUTER JOIN dbo.AccountStatusTransition ast (nolock)
ON a.SAID = ast.SAID
JOIN dbo.AccountPhone aph (nolock)
ON a.SAID = aph.SAID
and aph.sysphonetypeid in (1,8)
Join dbo.phonenumber pnh (nolock)
ON aph.sysphonenumberid = pnh.sysphonenumberid
left outer join timezone tz (nolock)
on pnh.areacode = tz.areacode
where a.accountstateid = 40
and a.createddt >= '11-17-12'
and a.createddt < '11-24-12'
and pnh.AreaCode is not null
and pnh.Exchange is not null
and pnh.PNumber is not null
;WITH TempTable1
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY CID, AID ORDER BY AID) Row,
CID, AID, SAID, name, hp, Spanish, timezone
FROM #ACTemp
Union All
SELECT CID, Temp.AID, Temp.SAID, Temp.name, Temp.hp, Temp.spanish, t1.hp, temp.timezone
FROM #ACTemp as temp
Inner JOIN TempTable1 t1 ON t1.Row = 2 AND temp.CID = t1.CID AND temp.AID = t1.AID
WHERE t1.Row = 1
)
insert into livetable (CID,AID,SAID,name,hp,spanish,timezone)
SELECT * FROM TempTable1
December 3, 2012 at 11:01 am
Hi and welcome to SSC. I would love to help but there are not enough details in what you posted to be much help. Please take a look at the first link in my signature for best practices when posting questions.
Also, why all the NOLOCK hints? They are not a magic go fast pill. Here are a couple articles you should about that query hint.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 3, 2012 at 11:11 am
Thanks for the reply. The nolocks were there when I came to the company. They love to use them.
I am getting the following errors:
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "CID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "AID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "SAID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "Name" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "hp" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "Spanish" of recursive query "TempTable1".
I have tried using CAST for each field, but it has not helped.
December 3, 2012 at 11:17 am
bbaine (12/3/2012)
Thanks for the reply. The nolocks were there when I came to the company. They love to use them.
You should have somebody there read those articles. Then ask them if they like to get incorrect data sporadically and have bugs that are impossible to debug because you can't reproduce them. 😛
I am getting the following errors:
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "CID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "AID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "SAID" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "Name" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "hp" of recursive query "TempTable1".
Msg 240, Level 16, State 1, Line 43
Types don't match between the anchor and the recursive part in column "Spanish" of recursive query "TempTable1".
I have tried using CAST for each field, but it has not helped.
I will venture to guess that you didn't read the article I posted because you still didn't post any details. I can tell you that you have some columns in a different order from the first query to the second query in your union all section. I have no idea what datatypes you have going on but that is where I would start.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply