CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
radonyzsolt
radonyzsolt

Reputation: 78

Invalid column name MSSQL 2008 with PIVOT

I am making some application and I need some rows from a Stored Procedure. And I need to insert them into Datatable. So here is my query:

    WITH OrderedResults AS ( select ROW_NUMBER() OVER (ORDER BY tert) AS 'RowNumber', tert, kert, ktgszalsz,szlasz, szlanev from dbo.[func_karton]('','5000','99999999999','0','99999999999','1','99999999999','2014-01-01','2014-01-31') where ktgszalsz in (61,611,6211,6311,6411,6511,6611,6711,6811) union select ROW_NUMBER() OVER (ORDER BY tert) AS 'RowNumber', tert, kert, '0' AS ktgszalsz,szlasz, szlanev from dbo.[func_karton]('','5000','99999999999','0','99999999999','1','99999999999','2014-01-01','2014-01-31') where ktgszalsz not in (61,611,6211,6311,6411,6511,6611,6711,6811)) 
SELECT ROW_NUMBER() OVER (ORDER BY a61) AS 'Row',* FROM (
    SELECT * FROM ( 
        SELECT LTRIM(szlanev) as 'name',('a'+ktgszalsz) as ktgszalsz, SUM(tert)- sum(kert) as 'value' FROM OrderedResults group by ktgszalsz,szlanev
    ) a
    pivot( 
        max(value) for ktgszalsz in (a0,a61,a611,a6211,a6311,a6411,a6511,a6611,a6711,a6811)
    ) piv 
)as b WHERE Row BETWEEN 1 AND 5

When I run it I have this error message:

Error: Invalid column name 'Row'

But it when I run it without the WHERE condition I have a "Row" column.

Upvotes: 0

Views: 156

Answers (1)

potashin
potashin

Reputation: 44581

You can' use aliases in WHERE clause, so use the following:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY a61) AS [Row] <...>
) WHERE [Row] BETWEEN 1 AND 5

Upvotes: 1

Related Questions