SQL syntax error: Multi-part identifier could not be bound for ROW_NUMBER()

Question

Need to solve this

This is for a REST API and i need count on each row so that i can use it for Pagination purposes.

SELECT *
FROM   (SELECT ROW_NUMBER()
                 OVER (
                   ORDER BY sc.LEGACY_PARTY_ID) AS rownum,
               *
        FROM   (SELECT *,
                       ROW_NUMBER()
                         OVER (
                           ORDER BY sc.LEGACY_PARTY_ID) AS rownuminner
                FROM   (SELECT *
                        FROM   (SELECT sc.legacy_party_id,
                                       sc.practice,
                                       sr.risk_level
                                FROM   dbo.supplier sc
                                INNER JOIN dbo.risk sr
                                  ON sc.LEGACY_PARTY_ID = sr.LEGACY_PARTY_ID)AS z) AS a)AS c
        WHERE  rownuminner <= ?)c
WHERE  rownum > ?;

Need elements of a table with row_number for each row but getting this error:

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "sc.LEGACY_PARTY_ID" could not be bound.

Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "sc.LEGACY_PARTY_ID" could not be bound.

LEGACY_PARTY_ID has the qualifier sc, but there are no objects aliased as sc in that query. — Thom A
– Thom A ♦, Commented Apr 24, 2019 at 11:30
I also recommend against the reuse of aliases. But FROM (SELECT * FROM (SELECT * FROM MIP.SUPPLIER_INFO) AS z ) AS z ) could simply be written as FROM MIP.SUPPLIER_INFO AS z Why so many parenthesis? — Thom A
– Thom A ♦, Commented Apr 24, 2019 at 11:32
Also, you have WHERE rownum > 0, however, rownum will never have a value <= 0. ROW_NUMBER() returns a positive value of 1 or more. — Thom A
– Thom A ♦, Commented Apr 24, 2019 at 11:34
sc is only in scope inside the derived table. Outside the derived table you need to refer to the derived table alias not the original alias the column was sourced from. With the full query there is still no need for that level of nesting — Martin Smith
– Martin Smith, Commented Apr 24, 2019 at 11:41

Thom A · Accepted Answer · 2019-04-24 11:43:03Z

4

You're trying to reference an object by an alias that is within a subquery. You need to reference it by the correct alias:

SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c.LEGACY_PARTY_ID) AS rownum,
             *
      FROM (SELECT *,
                   ROW_NUMBER() OVER (ORDER BY a.LEGACY_PARTY_ID) AS rownuminner
            FROM (SELECT *
                  FROM (SELECT sc.legacy_party_id,
                               si.supplier_name,
                               si.supplier_description,
                               sc.practice,
                               sc.category,
                               sc.subcategory,
                               sui.industry,
                               sr.risk_level
                        FROM mip.supplier_classification AS sc
                             INNER JOIN mip.supplier_info AS si ON si.legacy_party_id = sc.LEGACY_PARTY_ID
                             INNER JOIN mip.SUPPLIER_INDUSTRY AS sui ON sc.LEGACY_PARTY_ID = sui.LEGACY_PARTY_ID
                             INNER JOIN mip.SUPPLIER_RISK AS sr ON sc.LEGACY_PARTY_ID = sr.LEGACY_PARTY_ID) AS z ) AS a ) AS c
      WHERE rownuminner <= 10) AS c
WHERE rownum > 5;

This still seems over complicated though. Looking at it, it could likely be far more simply written as:

WITH CTE AS
    (SELECT sc.legacy_party_id,
            si.supplier_name,
            si.supplier_description,
            sc.practice,
            sc.category,
            sc.subcategory,
            sui.industry,
            sr.risk_level,
            ROW_NUMBER() OVER (ORDER BY sc.legacy_party_id) AS RN
     FROM mip.supplier_classification AS sc
          INNER JOIN mip.supplier_info AS si ON si.legacy_party_id = sc.LEGACY_PARTY_ID
          INNER JOIN mip.SUPPLIER_INDUSTRY AS sui ON sc.LEGACY_PARTY_ID = sui.LEGACY_PARTY_ID
          INNER JOIN mip.SUPPLIER_RISK AS sr ON sc.LEGACY_PARTY_ID = sr.LEGACY_PARTY_ID)
SELECT *
FROM CTE
WHERE RN <= ?
  AND RN > ?;

answered Apr 24, 2019 at 11:43

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Schultheiss Over a year ago

Second example is better IMHO here, so I think we agree there.

Ram Over a year ago

can you update your answer to remove sensitive data. Thanks

Thom A Over a year ago

There isn't any in there @Ram .

ShivShankar Namdev · Accepted Answer · 2019-04-24 11:45:31Z

SELECT *
FROM   (SELECT ROW_NUMBER()
                 OVER (
                   ORDER BY c.LEGACY_PARTY_ID) AS rownum,
               *
        FROM   (SELECT *,
                       ROW_NUMBER()
                         OVER (
                           ORDER BY c.LEGACY_PARTY_ID) AS rownuminner
                FROM   (SELECT *
                        FROM   (SELECT sc.legacy_party_id,
                                       si.supplier_name,
                                       si.supplier_description,
                                       sc.practice,
                                       sc.category,
                                       sc.subcategory,
                                       sui.industry,
                                       sr.risk_level
                                FROM   mip.supplier_classification sc
                                       INNER JOIN mip.supplier_info si
                                               ON si.legacy_party_id = sc.LEGACY_PARTY_ID
                                       INNER JOIN mip.SUPPLIER_INDUSTRY sui
                                               ON sc.LEGACY_PARTY_ID = sui.LEGACY_PARTY_ID
                                       INNER JOIN mip.SUPPLIER_RISK sr
                                               ON sc.LEGACY_PARTY_ID = sr.LEGACY_PARTY_ID)AS z) AS a)AS c
        WHERE  rownuminner <= ?)c
WHERE  rownum > ?;

You have defined c alias for outer queries and using sc so it was giving error

Collectives™ on Stack Overflow

SQL syntax error: Multi-part identifier could not be bound for ROW_NUMBER()

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related