August 3, 2009 at 2:18 am
Hi all,
I'm having error when the calculated field is used as @sortExpression in ROW_NUMBER() OVER (calculatedField) for my pagination. The error : "Invalid column name 'calulatedField'.
My query
With SQLPaging
As
(
SELECT pk, field_A + field_B as field_C
ROW_NUMBER() OVER (ORDER BY field_C) as rowNum
FROM myTable
)
select * from SQLPaging where rowNum > 1
The following query is working, but doesn't work with complex field, eg. Using CASE.
With SQLPaging
As
(
SELECT pk, field_A + field_B as field_C
ROW_NUMBER() OVER (ORDER BY field_A + field_B) as rowNum
FROM myTable
)
select * from SQLPaging where rowNum > 1
Is the a way to make it sortable by complex calculated field?
Please advise, thanks.
August 3, 2009 at 1:34 pm
You could use a derived table in the From clause:
With SQLPaging
As
(
SELECT pk, field_C
ROW_NUMBER() OVER (ORDER BY field_C) as rowNum
From (
SELECT pk, field_A + field_B as field_C
FROM myTable
)
select * from SQLPaging where rowNum > 1
August 3, 2009 at 2:54 pm
I don't think it's necessary to use both a CTE and a derived table here. The following is untested, but should work.
WITH SQLPaging AS (
SELECT pk, field_A + field_B as field_C
FROM myTable
)
SELECT pk, field_C
ROW_NUMBER() OVER (ORDER BY field_C) as rowNum
From SQLPaging
WHERE rowNum > 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 3:31 pm
After first fixing the missing comma after field_c in your code, you will get the following error:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'rowNum'.
You can't use the column alias for the column containing the row_number() function in the WHERE clause, so you do need to use a second CTE.
August 3, 2009 at 3:38 pm
Yeah, I noticed that the alias would not be recognized after I posted. I really should have tested before posting. I still prefer a CTE to a derived table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 7:32 pm
Lynn Pettis (8/3/2009)
After first fixing the missing comma after field_c in your code, you will get the following error:Msg 207, Level 16, State 1, Line 11
Invalid column name 'rowNum'.
You can't use the column alias for the column containing the row_number() function in the WHERE clause, so you do need to use a second CTE.
drew.allen (8/3/2009)
Yeah, I noticed that the alias would not be recognized after I posted. I really should have tested before posting. I still prefer a CTE to a derived table.Drew
Hi, I'm new to CTE and have no idea on how to use 2 CTE together. Do you mind to post a sample?
Thanks a lot. 🙂
August 3, 2009 at 7:56 pm
Using the code from earlier in the thread, here is how it could be written using two CTE's.
with myTableCTE as ( -- This CTE creates the column field_C that is used in the SQLPaging CTE
select
pk,
field_A + field_B as field_C
from
myTable
)
, SQLPaging
as
(
select
pk,
field_C,
row_number() over (order y field_C) as rowNum
from
myTableCTE
)
select
*
from
SQLPaging
where
rowNum > 1;
August 4, 2009 at 4:41 am
It's working! Thanks a lot, Lynn Pettis and Allen 😀
August 4, 2009 at 9:37 am
yaya1899 (8/4/2009)
It's working! Thanks a lot, Lynn Pettis and Allen 😀
Who is this "Allen" that you are referring to?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2009 at 10:27 am
The following seems to work OK just using a single derived table.
Create and load test data:
-- Create test table
create table #t
(
PKintnot null identity(1,1) primary key clustered,
field_Abigintnot null,
field_Bbigintnot null
)
--Load random test data
insert into #t
select top 15
a.id,
b.id
from
sys.sysobjects a,
sys.sysobjects b
order by
newid()
Query with derived table:
select
*
from
(
select
PK,
field_a + field_b as field_c,
row_number() over (order by field_a + field_b) as rownum
from
#t
) a
where
rownum > 1
drop table #t
Results:
(15 row(s) affected)
PK field_c rownum
----------- -------------------- --------------------
7 80 2
8 115 3
12 157 4
11 159 5
1 21575165 6
10 21575193 7
2 325576205 8
15 325576249 9
3 2009058206 10
4 2041058365 11
6 2057058424 12
13 2073058465 13
5 2302634277 14
9 4050116500 15
(14 row(s) affected)
August 4, 2009 at 7:34 pm
drew.allen (8/4/2009)
yaya1899 (8/4/2009)
It's working! Thanks a lot, Lynn Pettis and Allen 😀Who is this "Allen" that you are referring to?
Drew
I'm referring to you, sorry for my mistake.:-)
Michael Valentine Jones (8/4/2009)
The following seems to work OK just using a single derived table.Create and load test data:
...
Hi Micheal,
Thanks for your sample.
Here is my sample.
-- * Sameple - ORDER BY Calulated/Computed Field for Pagination --
DECLARE @test-2 TABLE (pk INT IDENTITY(1,1), field_A INT, field_B INT, field_C VARCHAR(12))
INSERT INTO @test-2 VALUES (1,2,'1')
INSERT INTO @test-2 VALUES (1,3,'1')
INSERT INTO @test-2 VALUES (4,2,'1')
INSERT INTO @test-2 VALUES (5,3,'1')
INSERT INTO @test-2 VALUES (2,3,'1')
INSERT INTO @test-2 VALUES (2,4,'1')
;
WITH myTable AS
(
SELECT *, field_A + field_B AS field_D
FROM @test-2
)
, SQLPaging AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY field_D) AS rowNum FROM myTable
)
SELECT * FROM SQLPaging WHERE rowNum > 1
August 4, 2009 at 8:44 pm
Please tell me you aren't using the * in the SELECT clause in your production code. You really should explicitly declare the columns you are returning in a select statement, even if you are returning all columns in the table.
August 5, 2009 at 3:36 am
Lynn Pettis (8/4/2009)
Please tell me you aren't using the * in the SELECT clause in your production code. You really should explicitly declare the columns you are returning in a select statement, even if you are returning all columns in the table.
Thanks for reminding me. I only use SELECT * for myTable and SQLPaging, since all the required columns already decalred in the inner SELECT. Would that cause any issue?
Thanks.
August 5, 2009 at 6:29 am
You should still explicitly name the columns in the SELECT clause. Not that it may cause problems, it is just that is, for many of us, a bast practice.
August 6, 2009 at 7:12 pm
Lynn Pettis (8/5/2009)
You should still explicitly name the columns in the SELECT clause. Not that it may cause problems, it is just that is, for many of us, a bast practice.
Hi Lynn Pettis,
Thanks again for your advice. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply