quote:

---

Originally posted by bmann

Hello,

I am running the following query in SQL 2005. And get the error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ServicePointChannel.SpcNodeid" could not be bound.

select

a.AccountID,
a.Status as AccountStatus,Reading.NodeID
,ReadingPeriod.StartTime
,ReadingPeriod.EndTime
--,Reading.ReceivedTime
,Reading.DataValue
--,dbo.fn_DecodeStatus(Reading.Status) AS Status

from node

INNER JOIN Reading ON ServicePointChannel.SpcNodeid = Reading.nodeid
INNER JOIN ReadingPeriod ON ServicePointChannel.SpcNodeid = ReadingPeriod.Spcnodeid
INNER JOIN ServicePointChannel on ServicePointChannel.NodeKey = node.NodeKey
INNER JOIN NodeLink on NodeLink.RightNodeKey = ServicePointChannel.NodeKey
INNER JOIN ServicePoint ON ServicePoint.NodeKey = NodeLink.LeftNodeKey
INNER JOIN codevalue cv on servicepoint.commoditytypekey = cv.codevaluekey

INNER JOIN (select
spc.NodeKey as SPCNodeKey,
spc.ServicePointChannelKey,
spc.SpcNodeid,
ic.IntervalLength,
'Interval' as ChannelType,
ic.ChannelNumber,
ic.NodeKey as ChannelNodeKey,
nl.NodeLinkKey,
ic.EffectiveStartDate as ChannelEffectiveStartDate,
ic.EffectiveEndDate as ChannelEffectiveEndDate,
nl.EffectiveStartDate as NodeLinkEffectiveStartDate,
nl.EffectiveEndDate as NodeLinkEffectiveEndDate

from
ServicePointChannel spc
join NodeLink nl on spc.NodeKey = nl.LeftNodeKey
join NodeLinkReason nlr on nl.NodeLinkReasonKey = nlr.NodeLinkReasonKey and nlr.ReferenceID = 'ServicePointChannel_To_IntervalChannel'

join IntervalChannel ic on nl.RightNodeKey = ic.NodeKey) as ChannelJoin
on ServicePointChannel.ServicePointChannelKey = ChannelJoin.ServicePointChannelKey
left outer join NodeLink nl2 on nl2.RightNodeKey = ServicePointChannel.NodeKey and nl2.NodeLinkReasonKey in (

select NodeLinkReasonKey from NodeLinkReason where ReferenceID = 'AccountServicePointChannel_To_ServicePointChannel'

)
left outer join AccountServicePointChannel aspc on nl2.LeftNodeKey = aspc.NodeKey
left outer join NodeLink nl3 on nl3.RightNodeKey = aspc.NodeKey and nl3.NodeLinkReasonKey in (

select NodeLinkReasonKey from NodeLinkReason where ReferenceID = 'Account_To_AccountServicePointChannel'
)
left outer join Account a on a.NodeKey = nl3.LeftNodeKey
order by a.accountid, starttime asc


Can someone check this out because i'm a bit lost.

---

SELECT		a.AccountID,
		a.Status AS AccountStatus,
		Reading.NodeID,
		ReadingPeriod.StartTime,
		ReadingPeriod.EndTime,
		Reading.DataValue
FROM		Node
INNER JOIN	ServicePointChannel ON ServicePointChannel.NodeKey = Node.NodeKey
INNER JOIN	Reading ON Reading.NodeID = ServicePointChannel.SpcNodeID
INNER JOIN	ReadingPeriod ON ReadingPeriod.SpcNodeID = ServicePointChannel.SpcNodeID
INNER JOIN	NodeLink ON NodeLink.RightNodeKey = ServicePointChannel.NodeKey
INNER JOIN	ServicePoint ON ServicePoint.NodeKey = NodeLink.LeftNodeKey
INNER JOIN	CodeValue AS cv ON cv.CodeValueKey = ServicePoint.CommodityTypeKey
INNER JOIN	(
			SELECT		spc.ServicePointChannelKey
			FROM		ServicePointChannel AS spc
			INNER JOIN	NodeLink AS nl ON nl.LeftNodeKey = spc.NodeKey
			INNER JOIN	NodeLinkReason AS nlr ON nlr.NodeLinkReasonKey = nl.NodeLinkReasonKey
			INNER JOIN	IntervalChannel AS ic on ic.NodeKey = nl.RightNodeKey
			WHERE		nlr.ReferenceID = 'ServicePointChannel_To_IntervalChannel'
		) AS cj ON cj.ServicePointChannelKey = ServicePointChannel.ServicePointChannelKey
LEFT JOIN	(
			SELECT		nl.RightNodeKey,
					nl.LeftNodeKey
			FROM		NodeLink AS nl
			INNER JOIN	NodeLinkReason AS nlr ON nlr.NodeLinkReasonKey = nl.NodeLinkReasonKey
			WHERE		nlr.ReferenceID = 'AccountServicePointChannel_To_ServicePointChannel'
		) AS nl2 ON nl2.RightNodeKey = ServicePointChannel.NodeKey
LEFT JOIN	AccountServicePointChannel AS aspc ON aspc.NodeKey = nl2.LeftNodeKey
LEFT JOIN	(
			SELECT		nl.RightNodeKey,
					nl.LeftNodeKey
			FROM		NodeLink AS nl
			INNER JOIN	NodeLinkReason AS nlr ON nlr.NodeLinkReasonKey = nl.NodeLinkReasonKey
			WHERE		nlr.ReferenceID = 'Account_To_AccountServicePointChannel'
		) AS nl3 ON nl3.RightNodeKey = aspc.NodeKey
LEFT JOIN	Account AS a ON a.NodeKey = nl3.LeftNodeKey
ORDER BY	a.AccountID,
		ReadingPeriod.StartTime