Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I have multiple identifier Could not be bound when I use ROW_NUMBER()


I have multiple identifier Could not be bound when I use ROW_NUMBER()

Author
Message
bkshn
bkshn
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 125
Hello

I want to have paging in my list.

I use code below

   declare @string nvarchar(MAX)
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList '5C34A797-DCCB-40FF-B7AD-F6512803065E','20','0','0','10','1','2','','' -- add by kashani
select * into #temp1 from @UserMidListTable


SET @string=''
SELECT TOP 50 @string = ISNULL('<tr> <td><a class="class_a" href="'
+ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,90),'HTTP://namayeshgah.com')
+'" target=_self> <div id="class_ans"> '
+ ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1, 150),'')
+' </div> </a></td></tr>','')
+ @string COLLATE DATABASE_DEFAULT from
(SELECT top 100 percent maintable.ID, ROW_NUMBER() OVER (ORDER BY maintable.ID ) AS Row
FROM exhibitor.dbo.maintable INNER JOIN #temp1 ON exhibitor.dbo.maintable.ID = [#temp1].[ValueMid]
inner join exhibitor.dbo.blgbelongs on exhibitor.dbo.blgbelongs.Table1ID=[#temp1].[ValueMid]
where exhibitor.dbo.blgbelongs.table1ID in ( SELECT table1ID
From exhibitor.dbo.Blgbelongs
WHERE (exhibitor.dbo.blgBelongs.Table1 = 20) AND (exhibitor.dbo.blgBelongs.Table2 = 12)))


I think because of line

(SELECT top 100 percent maintable.ID, ROW_NUMBER() OVER (ORDER BY maintable.ID ) AS Row

I have an error " multiple identifier "exhibitor.dbo.maintable.Mid" could not be bound " in line

(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1, 150)

Could you help me?
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 309
multiple identifier "exhibitor.dbo.maintable.Mid"

you could have used this Column "Mid" from the table "MainTable" from the Exhibitor DB,

the SQL tells you that, particular column you are trying to use is not available / Exists / Not found.

find where you have used this column , either in a SELECT / JOIN / WHERE, once you found this , then it would be resolved.
Nevyn
Nevyn
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 3149
Nothing to do with the row_number() I don't think. But I am curious why you are using row_number() in a derived table, and then never using that number.

You are building a derived table, but when selecting from it you are referring to tables within it instead of the derived table itself.

From the point of view of your select top 50, the table "exhibitor.dbo.maintable" doesn't exist (its not in the from clause).
bkshn
bkshn
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 125
Hello
I think I use it in from statement

FROM exhibitor.dbo.maintable INNER JOIN #temp1

isn't it?
ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
bkshn (12/14/2013)
Hello
I think I use it in from statement

FROM exhibitor.dbo.maintable INNER JOIN #temp1

isn't it?


There's quite a lot wrong with your statement. Here's a brief analysis:
declare @string nvarchar(MAX)
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))

insert into @UserMidListTable (Id, Mid, ValueMid, CatParent, [Enabled], LastUpdate, Company)
exec UserMidList '5C34A797-DCCB-40FF-B7AD-F6512803065E','20','0','0','10','1','2','','' -- add by kashani

SELECT * INTO #temp1 FROM @UserMidListTable

SET @string=''

SELECT TOP 50
@string =
ISNULL(
'<tr> <td><a class="class_a" href="'
+ ISNULL(exhibitor.dbo.prpReadTypeID(d.Mid,d.ID,1,90),'HTTP://namayeshgah.com')
+ '" target=_self> <div id="class_ans"> '
+ ISNULL(exhibitor.dbo.prpReadTypeID(d.Mid,d.ID,1, 150),'') -- WHERE DOES [Mid] COME FROM??
+ ' </div> </a></td></tr>'
,'')
+ @string
COLLATE DATABASE_DEFAULT
FROM (
SELECT top 100 percent -- WHY?
m.ID,
ROW_NUMBER() OVER (ORDER BY m.ID) AS [Row] -- WHERE IS THIS USED?

FROM exhibitor.dbo.maintable m

INNER JOIN #temp1 t
ON m.ID = t.ValueMid

INNER JOIN exhibitor.dbo.blgbelongs b
ON b.Table1ID = t.ValueMid

WHERE b.table1ID IN
(SELECT bi.table1ID FROM exhibitor.dbo.Blgbelongs bi WHERE bi.Table1 = 20 AND bi.Table2 = 12)
) d -- MISSING IN ORIGINAL CODE




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
bkshn
bkshn
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 125
Hello ChrisM@home
let me explain my question again.

I want to do paging with Row_Number on several Join Table.
My temp table fill by executing UserMidlist Procedure

I use code below
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList '5C34A797-DCCB-40FF-B7AD-F6512803065E','20','0','0','10','1','2','','' -- add by kashani
select * into #temp from @UserMidListTable

select exhibitor.dbo.maintable.ID as maintableid,exhibitor.dbo.maintable.MID as maintableMid,exhibitor.dbo.maintable.OwnerID as maintableOwnerID,exhibitor.dbo.maintable.schemaID as maintableSchemaID,exhibitor.dbo.blgbelongs.Table1ID as blgTable1,exhibitor.dbo.blgbelongs.Table2ID as blgTable2
from (select exhibitor.dbo.maintable.ID as maintableid,exhibitor.dbo.maintable.MID as maintableMid,exhibitor.dbo.maintable.OwnerID as maintableOwnerID,exhibitor.dbo.maintable.schemaID as maintableSchemaID,exhibitor.dbo.blgbelongs.Table1ID as blgTable1,exhibitor.dbo.blgbelongs.Table2ID as blgTable2
,ROW_NUMBER over (order by exhibitor.dbo.maintable.ID ) as ROW
FROM #temp INNER JOIN exhibitor.dbo.maintable ON [#temp].[ValueMid]=exhibitor.dbo.maintable.ID inner join
exhibitor.dbo.blgbelongs on exhibitor.dbo.blgbelongs.Table1ID= exhibitor.dbo.maintable.ID
where exhibitor.dbo.blgbelongs.ID in (SELECT b2.id
From exhibitor.dbo.Blgbelongs as b2
where b2.blgid in (select id
from exhibitor.dbo.blgid bi
where bi.table1id=20 and bi.table2id=12))) as JOINTable

JOINTable.ROW BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)





I have multiple identifier couldn't be bound in both my select statement.

I should say I use alias name for my from clause and I solve my problem, is it ok?

Thanks for your guide
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 309
Hi,

Yes, you have mentioned it in the SELECT Statement, but what's the reason is, it is simply a namespace error,



Check for the column Name in the MainTable.

Thanks,
Prabhu
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
bkshn (12/15/2013)
Hello ChrisM@home
let me explain my question again.

I want to do paging with Row_Number on several Join Table.
My temp table fill by executing UserMidlist Procedure

I use code below
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList '5C34A797-DCCB-40FF-B7AD-F6512803065E','20','0','0','10','1','2','','' -- add by kashani
select * into #temp from @UserMidListTable

select exhibitor.dbo.maintable.ID as maintableid,exhibitor.dbo.maintable.MID as maintableMid,exhibitor.dbo.maintable.OwnerID as maintableOwnerID,exhibitor.dbo.maintable.schemaID as maintableSchemaID,exhibitor.dbo.blgbelongs.Table1ID as blgTable1,exhibitor.dbo.blgbelongs.Table2ID as blgTable2
from (select exhibitor.dbo.maintable.ID as maintableid,exhibitor.dbo.maintable.MID as maintableMid,exhibitor.dbo.maintable.OwnerID as maintableOwnerID,exhibitor.dbo.maintable.schemaID as maintableSchemaID,exhibitor.dbo.blgbelongs.Table1ID as blgTable1,exhibitor.dbo.blgbelongs.Table2ID as blgTable2
,ROW_NUMBER over (order by exhibitor.dbo.maintable.ID ) as ROW
FROM #temp INNER JOIN exhibitor.dbo.maintable ON [#temp].[ValueMid]=exhibitor.dbo.maintable.ID inner join
exhibitor.dbo.blgbelongs on exhibitor.dbo.blgbelongs.Table1ID= exhibitor.dbo.maintable.ID
where exhibitor.dbo.blgbelongs.ID in (SELECT b2.id
From exhibitor.dbo.Blgbelongs as b2
where b2.blgid in (select id
from exhibitor.dbo.blgid bi
where bi.table1id=20 and bi.table2id=12))) as JOINTable

JOINTable.ROW BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)





I have multiple identifier couldn't be bound in both my select statement.

I should say I use alias name for my from clause and I solve my problem, is it ok?

Thanks for your guide


It won't solve your problem, no, because you're introducing more errors (brackets after ROW_NUMBER are not optional). Don't try to fix the whole query. Instead, break it down into sensible parts and test that they are working correctly before adding back the next layer of complexity. You have an inner query, known as a derived table, and an outer query. Work on the inner query in isolation from the outer query until the results meet your requirements. Here's a slight rewrite to help you along:
SELECT 
m.ID as maintableid,
m.MID as maintableMid,
m.OwnerID as maintableOwnerID,
m.schemaID as maintableSchemaID,
b.Table1ID as blgTable1,
b.Table2ID as blgTable2,
ROW_NUMBER() OVER (ORDER BY m.ID) as [ROW]
FROM #temp t
INNER JOIN exhibitor.dbo.maintable m
ON t.[ValueMid] = m.ID
INNER JOIN exhibitor.dbo.blgbelongs b
ON b.Table1ID = m.ID
WHERE b.ID in (
SELECT b2.id
FROM exhibitor.dbo.Blgbelongs b2
INNER JOIN exhibitor.dbo.blgid bi
ON bi.id = b2.blgid
AND bi.table1id = 20
AND bi.table2id = 12
)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search