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