Incorrect prefix error (select count statement)

Question

Incorrect prefix error (select count statement)

SQLPain

Hall of Fame

Points: 3522
More actions
October 7, 2015 at 11:34 am

#399153

Can somebody check my naming convention and see what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoID
Error message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27079 More actions · Answer 1

SQLPain (10/7/2015)
Can somebody check my naming convention and see what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoID
Error message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

You aliased the table "Channels" as C, but are using "Channels.ChannelID" in the WHERE clause.

WHERE Channels.ChannelID = c.ChannelID

This doesn't look right. Does it even need to be there?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

g.britton SSChampion Points: 13863 More actions · Answer 2

You aliased table Chanels as c, effectively hiding Chanels so you can't use it anymore. Replace Chanels. with c.

Gerald Britton, Pluralsight courses

SQLPain Hall of Fame Points: 3522 More actions · Answer 3

Also,

I have the following select statement and it is working fine, I cant understand the logic behind it and not sure why is it even working as I haven't aliased channels.

,(SELECT nameshort FROM channels WHERE channelid = c.assignedbranch) AS Facilitator

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

Here is the whole query, everything seems fine except for the count statements & Facilitator column in the select statement(although the facilitator column is giving me somehow the expected results)

Can someone review the count statements:

SELECT DISTINCT

C.OldDealerID

,C.NameLong

,C.NameShort

,C.MailingState

,CC.NameFirst

,CC.NameLast

,CC.Phone

,CC.FaxPhone

,MR.Name

,CC.Address

,CC.City

,CC.Zip

,C.MailingStreet

,C.MailingCity

,CC.EMailAddress

,(SELECT nameshort FROM channels WHERE channelid = c.assignedbranch) AS Facilitator

,C.DateLastReview

,HC.HoldingCoDescription

--Funded, Approved,Conditioned, Declined

/*,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail = 'A' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ApprovedLastYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail ='I' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ConditionedLastYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail = 'D' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As DeclinedLastYear

,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedThisYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail = 'A' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ApprovedThisYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail ='I' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ConditionedThisYear

,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where Channels.ChannelID = c.ChannelID and StatusAppDetail = 'D' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As DeclinedThisYear

*/

FROM Channels AS C

INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID

INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID

INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID

INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoID

WHERE

C.ChannelType IN (1, 6)

AND C.OldDealerID <> 1234

AND C.ActiveFlag = 'Y'

AND CC.Relationship = 1

ORDER BY C.OldDealerID

g.britton SSChampion Points: 13863 More actions · Answer 5

This part:

Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID

makes no sense to me. First you aliased the Channels table as c "From Channels c left join..." then you try to refer to the Channels table in the where clause "Where Channels.ChannelID = c.ChannelID". You can't use Channels there, since you aliased it to c. However if you fix it like this:

Where c.ChannelID = c.ChannelID

then the where clause is always true. That can't be what you want