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)


    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


    [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 = 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

  • 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@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

Viewing 8 posts - 1 through 7 (of 7 total)

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