I have multiple identifier Could not be bound when I use ROW_NUMBER()
-
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?
-
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.
-
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).
-
Hello
I think I use it in from statement
FROM exhibitor.dbo.maintable INNER JOIN #temp1
isn't it?
-
bkshn (12/14/2013)
HelloI 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
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
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 = 6declare @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
-
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
-
bkshn (12/15/2013)
Hello ChrisM@homelet 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 = 6declare @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:
SELECTm.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 ShawFor 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply