CODEWITHSUNDEEP
sql-server
Ram
Ram

Reputation: 3

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

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.

Upvotes: 0

Views: 621

Answers (2)

ShivShankar Namdev
ShivShankar Namdev

Reputation: 298

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

Upvotes: 0

Thom A
Thom A

Reputation: 95554

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 > ?;

Upvotes: 4

Related Questions