March 19, 2021 at 10:38 am
Have a requirement of case , pagination in Order by clause. It looks something like this. But getting error. how to fix
ORDER BY
case when (@PageCount <= 0 OR @PageIndex <= 0) then c.fullname
else ( c.fullname OFFSET (@PageCount * (@PageIndex - 1)) ROWS FETCH NEXT @PageCount ROWS ONLY )
end
March 19, 2021 at 2:03 pm
If you told us the text of the error message it might help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 19, 2021 at 2:08 pm
In a CASE <condition> THEN <result>, the <result> must be a single value (technically called a "scalar" value). Not allowed are SQL keywords or operators (>=, <) as part of the THEN result.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 19, 2021 at 4:01 pm
Hmm... you wouldn't put the pagination in the order by clause. The order by clause is used to define the result set then the pagination filters it.
March 23, 2021 at 7:33 am
The idea is to do pagination in order by if count = 0 then one column only else paginate.
when i set pagecount/pageindex value >0 it works but fails for 0 with error:
Msg 10744, Level 15, State 1, Line 80
The number of rows provided for a FETCH clause must be greater then zero.
declare @PageCount int, @Pageindex int
set @PageCount = 2
set @pageindex = 2
select c.ClientFullName,(t.task_id) TaskID,
t.EXID,
t.clientid ClientID
from TM t
inner join CM c WITH(NOLOCK)
ON t.firm_id=c.firm_id and t.client_id=c.client_id
ORDER BY
case when (@PageCount <= 0 OR @PageIndex <= 0) then c.ClientFullName end
OFFSET (@PageCount * (@PageIndex - 1)) ROWS FETCH NEXT @PageCount ROWS ONLY
What i'm really tryin
.....
ORDER BY 1
OFFSET CASE
when (@PageCount <= 0 OR @PageIndex <= 0) then c.XCMClientFullName
else ((@PageCount * (@PageIndex - 1)) ROWS FETCH NEXT @PageCount ROWS ONLY )
end
g here is
March 25, 2021 at 6:14 pm
One of the major ideas of client/server architecture was that the system would come in tiers. The database layer would get a result set, then pass that set on to a display or presentation layer. Pagination is a display function, not a database function. Back in the days of COBOL, Fortran and other procedural languages, this concept did not exist. This is why one database can serve an unlimited number of front ends. Please go back and look at the first few weeks the class you had on database. What you're trying to do is wrong. I'm also kind of curious about the use of "<= 0" ; can you give me an example of a page count less than zero? It sounds like your printer is broken 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
March 25, 2021 at 6:34 pm
One of the major ideas of client/server architecture was that the system would come in tiers. The database layer would get a result set, then pass that set on to a display or presentation layer. Pagination is a display function, not a database function. Back in the days of COBOL, Fortran and other procedural languages, this concept did not exist. This is why one database can serve an unlimited number of front ends. Please go back and look at the first few weeks the class you had on database. What you're trying to do is wrong. I'm also kind of curious about the use of "<= 0" ; can you give me an example of a page count less than zero? It sounds like your printer is broken 🙂
If you have 1 million rows but the use might only want to browse through the first few pages of 100 or so rows, then it would not be efficient to return the full 1 million rows to the client.
March 25, 2021 at 8:07 pm
An ORDER BY either has an OFFSET clause or it doesn't. There is no "sometimes it does, sometimes it doesn't".
In this structure, rather than use 0 for the count, could you use 999999999 or some other huge number? That would put all returned rows in the first page, which seems to be what you want.
ORDER BY
case when (@PageCount <= 0 OR @PageIndex <= 0) then c.ClientFullName end
OFFSET (@PageCount * (@PageIndex - 1)) ROWS FETCH NEXT @PageCount ROWS ONLY
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply