SQL Server can't specify the same column twice, and aliasing is not working for me [closed]

eqqqjvef 于 2023-10-15 发布在 SQL Server

关注(0)|答案(1)|浏览(144)

Closed. This question is not reproducible or was caused by typos . It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed yesterday.
This post was edited and submitted for review yesterday.
Improve this question

I am building a dynamic product search query using c# and dapper. I am trying to use a Common Table Expression to filter the product results, and then to order the filtered results, and implement pagination on them.

I keep getting the error:
SqlException: The column 'ProductId' was specified multiple times for 'FilteredResults'.

Here is my query:

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT p.*, pv.* 
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    *, p.ProductId AS ProductIdAlias 
FROM 
    FilteredResults 
ORDER BY 
    p.ProductIdAlias 
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;

I am trying to use an alias, but it is not working. If I remove the ORDER BY p.ProductId clause in the pagination query, I then get this error:

SqlException: Incorrect syntax near '@queryOffset'.
Invalid usage of the option NEXT in the FETCH statement.

Here is the working query:

WITH FilteredProducts AS (
    SELECT
        p.ProductId,
        p.ProductName
    FROM Products p
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId
    WHERE 1 = 1
    AND pc.CategoryId = @categoryId
    AND (p.ProductName LIKE @searchText OR pd.DescriptionBody LIKE @searchText)
)
SELECT
    p.ProductId,
    p.ProductName,
    (
        SELECT
            pv.VariantId,
            pv.VariantName,
            pv.VariantPriceMain
        FROM ProductVariants pv
        WHERE p.ProductId = pv.ProductId
        FOR XML PATH('Variant'), ROOT('Variants'), TYPE
    ) AS Variant
FROM FilteredProducts p
WHERE EXISTS (
    SELECT 1
    FROM ProductVariants pv
    WHERE p.ProductId = pv.ProductId
      AND pv.VariantPriceMain >= @minPrice
);

sql-server

来源：https://stackoverflow.com/questions/77290356/sql-server-cant-specify-the-same-column-twice-and-aliasing-is-not-working-for

1条答案

按热度按时间

ecfdbz9o1#

When you have a CTE like this

WITH cte AS (
   SELECT *
   FROM [Table] p
)

The table alias p within the CTE is no longer valid outside the CTE. That is, you cannot do this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT p.*
FROM cte

All you can do is this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT cte.*
FROM cte

or this:

WITH cte AS (
   SELECT *
   FROM [Table] p
)
SELECT q.*
FROM cte q

This is because pno longer exists for the outer level. This is true even if there were multiple tables used in the CTE. The original table names used inside the CTE are no longer relevantoutside the CTE.

Additionally, column names returned from the CTE must be unique. If you have two tables in the CTE that both have columns with the same name, it is not enough to reference them separately by their table in the CTE's SELECT clause.

That is, you cannot do this:

WITH cte AS (
    SELECT t1.ProductID, t2.ProductID
    FROM t1
    INNER JOIN t2 ON ...
)

And you cannot do this (if there are shared column names):

WITH cte AS (
    SELECT t1.*, t2.*
    FROM t1
    INNER JOIN t2 ON ...
)

Which means you definitely cannot do this:

WITH cte AS (
    SELECT t1.ProductID, t2.ProductID
    FROM t1
    INNER JOIN t2 ON ...
)
SELECT t1.ProductID
FROM cte

Because all three snippets cause you to have two columns with the same ProductID name.

This should be obvious once we understand the first section about table names and aliases. If neither t1 nor t2 exist outside the CTE as usable identifiers, then neither do t1.ProductID or t2.ProductID . All you have is cte.ProductID , and this is ambiguous; it could reference the column from either table, and that's not legal.

Instead, you need to manually list out all the columns you need from both source tables, so no names are duplicated. This has always been the better option , even when no CTEs or duplicate column names are involved. Additionally, make sure to only reference the CTE itself outside of the CTE, rather than the source tables.

So it would look like this:

WITH cte AS (
    SELECT t1.ProductID As t1ProductID, t2.ProductID As t2ProductID
    FROM t1
    INNER JOIN t2 ON ...
)
SELECT cte.t1ProductID
FROM cte

For your query, it will looks like this (with the exception that you will want to add back more of the columns):

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT p.ProductID As ProductIDAlias 
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    f.ProductIdAlias 
FROM 
    FilteredResults  f
ORDER BY 
    f.ProductIdAlias 
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server can't specify the same column twice, and aliasing is not working for me [closed]

1条答案

相关问题

热门标签

最新问答