March 4, 2008 at 11:06 am
I'm stumped. I'm writing a fairly simple CTE to traverse a hierarchy and I'm running into a strange problem. The query looks like this:
with Transfers
(
CardNumber
,ActivationSystemTransactionID
,TransferFromCard
,TransferFromCardActivationSystemTransactionID
,TransferLevel
)
as
(
select cad.CardNumber
,cad.ActivationSystemTransactionID
,cad.TransferFromCard
,TransferFromCardActivationSystemTransactionID = null
,TransferLevel = 0
from dbo.CardActivationDetail cad
where cad.TransferFromCard is null
union all
select cad.CardNumber
,cad.ActivationSystemTransactionID
,cad.TransferFromCard
,TransferFromCardActivationSystemTransactionID = transfer.ActivationSystemTransactionID
,TransferLevel + 1
from dbo.CardActivationDetail cad
join dbo.CardActivationDetail transfer
on cad.TransferFromCard = transfer.CardNumber
)
select CardNumber
,ActivationSystemTransactionID
,TransferFromCard
,TransferFromCardActivationSystemTransactionID
,TransferLevel
from Transfers
The problem is with the TransferLevel column. SQL Server keeps telling me that it is an invalid column name in the "TransferLevel + 1" line (in bold). If I take the TransferLevel column completely out of the query it works, but I need it. I have other CTE's (and the samples from BOL) that are nearly identical and they work fine. Of course, they are in ohter databases... This database is running on 2005 compatibility mode, but I'm not aware of any other instance or database settings that would affect this.
Have you seen this before?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 4, 2008 at 11:30 am
you appear to be creating "TransferLevel" in the CTE.
select ..., TransferLevel = 0 will name the output column "TransferLevel".
Does "TransferLevel" exist in dbo.CardActivationDetail or dbo.CardActivationDetail? If not, that's the cause of your error.
I think you want to make UNION'd select recursive by joining to "Transfer" (the CTE).
March 4, 2008 at 12:40 pm
You need to replace, "join dbo.CardActivationDetail transfer" in the second part of the CTE, with, "join Transfers transfer".
That should solve it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2008 at 1:51 pm
Thank you, I figured I was doing something stupid like that, I just couldn't see it for anything.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply