Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I have multiple identifier Could not be bound when I use ROW_NUMBER() Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 4:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
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?
Post #1521833
Posted Wednesday, December 11, 2013 5:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:19 PM
Points: 86, Visits: 220
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.



Post #1521840
Posted Wednesday, December 11, 2013 9:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
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).
Post #1521956
Posted Saturday, December 14, 2013 5:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
Hello
I think I use it in from statement

FROM exhibitor.dbo.maintable INNER JOIN #temp1

isn't it?
Post #1522940
Posted Saturday, December 14, 2013 6:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 1,030, Visits: 6,677
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
Post #1522944
Posted Sunday, December 15, 2013 11:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
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
Post #1523094
Posted Monday, December 16, 2013 1:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:19 PM
Points: 86, Visits: 220
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
Post #1523118
Posted Monday, December 16, 2013 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1523180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse