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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply