Friday, March 9, 2012

Paging and Sorting Using ROWNUM()

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

Code Snippet

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

AND ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.ORDERBY = ' ORDER BY ' + @.SORT --END

--CASE WHEN @.Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

--SET @.TOPSQL = 'SELECT TOP ' + Convert(VarChar,@.PageSize) + '* FROM #RESULTS ' + @.ORDERBY

--SELECT @.TOPSQL

--EXEC (@.TOPSQL)

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.

I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)

What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!

If you want to have the paging for performance boost, then you have to follow these rules,

1. You should identify the unique value which identify the each row

2. These Unique columns should be sorted

(Example)

Using Sysobjects fetch 20 records per page

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.LastRow varchar(100)

)

as

Begin

Select Top(@.RowsPerPage) * into #T from Sysobjects

Where name > @.LastRow or @.LastRow is NULL

Order By Name;

--Data for Page

Select * from #T

--Last Row data for next page @.LastRow param

Select Top 1 Name from #T Order By Name Desc;

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, null

--Page 2

Exec FetchPagingData 20, 'COLUMN_DOMAIN_USAGE'

--Page 3

Exec FetchPagingData 20, 'database_permissions'

If you can’t satisfy above rule then you have to use the Row_Number(); but it wont give any performance boost. If you use .NET better you can cache it on the dataset & reuse the dataset for each page.

|||This isn't going to work...I need to use ROW_NUMBER()....

|||

If you use SQL Server 2005 then the following query might help you – it wont increase the performance, but you may cut down the network trafic.

Code Snippet

Create Proc FetchPagingData

(

@.RowsPerPage int,

@.Page int

)

as

Begin

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select * from CTE Where RowNum >(@.Page-1) * @.RowsPerPage

and RowNum <= (@.Page) * @.RowsPerPage Order By Name

--Total Records & page

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

--Page 1

Exec FetchPagingData 20, 1

--Page 2

Exec FetchPagingData 20, 2

--Page 3

Exec FetchPagingData 20, 3

|||

Maybe I'm being dense, but I'm not seeing how this addresses my problem. My issue is that I need to order a complete record set, and then break that entire set down in to pages.

In the FetchPagingData procedure, you're passing in the number of pages (which I have) and some @.Page variable, which I have no idea what that is.

Also, that proc is going against a static table, not a temp table. ANd there's no way to know how many pages I have until I perform the actual query...

This is how I it working but can't quite put my finger on how to make it work:

Let's say I'm sorting/paging on NAME. I run a query based on NAME which INSERT's the records into my temp table in ASCending order. There are 1014 records. The default number of records per page is 1000, thus there will be 2 pages in this query. The first page has records A. Dan Ryals through William Green - row numbers 1 through 1000. The send page has records William Pallister through Zhariff Hulagana - these are numbered 1 through 14.

Now let's say I choose to sort this by NAME DESCending, the behavior I expect to see is the ENTIRE result being resorted in DESCending order, thus invalidating the old row numbers. The first page should have records Zhariff Hulagana through the first 1000, and page 2 should have the next 14 records, the last one being A. Dan Ryals.

If I insert every record into my temp table and then try to select just the chosen rows I get an error that RowNum is undefined. Here's my snippet:

CREATE TABLE #RESULTS (

SEQ INT )

EXEC(@.SQL)

--IF @.SORT IS NULL EXECUTE COMPLETE SEARCH

IF @.COLUMN_NAME IS NULL OR @.COLUMN_NAME = 'AccountPAymentID'

BEGIN

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @.TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

SET @.OrderBy = CASE WHEN @.Sort IS NULL THEN '' ELSE ' ORDER BY ' + @.SORT END

--SELECT @.ORDERBY

--EXEC('SELECT * FROM #RESULTS ' + @.ORDERBY)

SELECT * FROM #RESULTS WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

SET @.PAGES = Round(@.totalRows / @.PageSize,0,1) + CASE WHEN @.TotalRows % @.PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @.PAGES AS PageCount,

@.TOTALROWS AS TotalRecords

|||

Ok. I got your point.

Let me clarify here,

You will pass the number of records on each page or number of pages you required.

Once the input passed you have to identify the each row's page number.

If you use SQL Server 2005 its very simple, we have to change the previous query slightly.

With Assumption1: Passing Number of Record for each page..

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfRowPerPage

(@.RowsPerPage int)

as

Begin

;With CTE

as

(

Select Name,Id,XType , Row_Number() Over(order By name) RowNumfrom Sysobjects

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages From Sysobjects

End

go

Exec FetchDataWithPaging_ForNoOfRowPerPage 1000

With Assumption 2 -- Passing Required Pages,

Code Snippet

Create Proc FetchDataWithPaging_ForNoOfPage

(@.RequiredPages int)

as

Begin

Declare @.TotalRecords as Int;

Declare @.RowsPerPage as Int;

Select Name,Id,XType into #t from Sysobjects;

Select @.TotalRecords = Count(*) From #t

Select @.RowsPerPage = Round(Cast(@.TotalRecords as float)/Cast(@.RequiredPages as float),0)

;With CTE

as

(

Select * , Row_Number() Over(order By name) RowNumfrom #T

)

Select *, ((RowNum-1)/@.RowsPerPage) + 1 as Page from CTE

Order By Name

--Total Recodrs & pages

Select Count(*) as TotalRows,Count(*)/@.RowsPerPage as TotalPages, @.RowsPerPage RowsPerPage From #T

End

go

Exec FetchDataWithPaging_ForNoOfPage 10

|||

So last dumb question:

So would I take my entire SELECT query, including the JOIN's and stick it in that CTE piece?

Is there another way, because frankly I am utterly confused as to how I would convert this query:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

--WHERE RowNum BETWEEN (((@.Page * @.PageSize) - @.PageSize) + 1) AND ((@.Page * @.PageSize) - @.PageSize) + @.PageSize

WHERE ((@.PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @.PayeeName + '%'))

AND ((@.AccountId IS NULL) OR (AC.AccountId = @.AccountId))

AND ((@.DistributionId IS NULL) OR (DS.DistributionId = @.DistributionId))

AND ((@.PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @.PaymentDate), 0))) -- Ignores the time

AND ((@.PaymentNumber IS NULL) OR (PY.AccountPaymentId = @.PaymentNumber))

AND ((@.IsReconciled IS NULL) OR (PY.ReconciledInd = @.IsReconciled))

AND ((@.AmountIssued IS NULL) OR (PY.PaymentAmount = @.AmountIssued))

AND ((@.AmountPaid IS NULL) OR (PY.AccountPaidAmount = @.AmountPaid))

AND ((@.IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @.IssueStatus))

AND ((@.AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @.AccountStatus))

ORDER BY AccountPaymentID

...into your query. Also, mine is already contained within a stored procedure.

I apologize for my paging ignorance. I'm not new to SQL Server, but I am new to the whole sorting thing via SQL Server so slowly picking it up. Is there any way to take what I already have and just tweak it a bit. The with CTE has me kind of baffled...

|||

Yes. You can do that. The query result will be act as simple table (only for writing query) on CTE.

|||

Last one, I swear...

I've altered my query to utilize a CTE as:

Code Snippet

BEGIN

DECLARE @.PageSize INT

SET @.PAgeSize = 1000

;

WITH CTE

AS

(

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description AS AccountDescription

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentID,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentID) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentID = PY.AccountPaymentID)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

)

Select *, ((RowNum-1)/@.PageSize) + 1 as Page from CTE

end

When I run the query, I'm getting the following error:

Msg 207, Level 16, State 1, Line 67

Invalid column name 'RowNum'.

Am I missing something?|||

You have to expose the Rownum in the SELECT clause of the toppermost query in the CTE:

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

....

, PE.clm_no

, CM.clmnt_idno

, SQ.RowNum

FROM ...

I think SQ is the right alias, but it is the right gist.

No comments:

Post a Comment