October 9, 2002 at 8:23 am
I have been asked if it is possible to set the number of rows returned by a view to 150.
I know that this can be done within a stored procedure or using a session variable. but the client software can only call the view
the source code of the view is shown below.
Create View CPDB
AS
SELECT
dbo.CPDB_View.pupsn,
dbo.CPDB_View.pgen,
dbo.CPDB_View.puprn,
dbo.CPDB_View.pfirm,
dbo.CPDB_View.pprem1,
dbo.CPDB_View.pprem2,
dbo.CPDB_View.phno1,
dbo.CPDB_View.phlet,
dbo.CPDB_View.phno2,
dbo.CPDB_View.pstr,
dbo.CPDB_View.pstr2,
dbo.CPDB_View.ptwn,
dbo.CPDB_View.ppstwn,
dbo.CPDB_View.ppar,
dbo.CPDB_View.pcnty,
dbo.CPDB_View.ppcod,
dbo.CPDB_View.pfeatr,
dbo.CPDB_View.pstat,
dbo.CPDB_View.pcat,
dbo.CPDB_View.pqual,
dbo.CPDB_View.pstrdt,
dbo.CPDB_View.penddt,
dbo.CPDB_View.pcurec,
dbo.CPDB_View.pconc,
case when pcat='01' then 'Domestic'
else (case when pcat='02' then 'Commercial'
else (case when pcat='03' then 'Domestic / Commercial'
else 'Miscellaneous'end)end)end as PropertyType,
case when IsolatedProperty ='T' then 'Isolated Property'end AS IsolatedProperty,
dbo.wm_rounds.RefuseDay,
dbo.wm_rounds.RefuseRound,
dbo.wm_rounds.RefuseWalkNumber,
dbo.wm_rounds.RefuseCollectTime,
dbo.wm_rounds.RecycleDay,
dbo.wm_rounds.RecycleRound,
dbo.wm_rounds.RecycleWalkNumber,
dbo.wm_rounds.RecycleCollectTime,
dbo.wm_rounds.Comments,
(select count(*)from calllog where (convert(varchar,dbo.CPDB_View.pupsn))=custid and callstatus<>'Closed'and callstatus<>'Resolved') as OpenCalls,
(select count(*)from calllog where (convert(varchar,dbo.CPDB_View.pupsn))=custid and (callstatus='Closed'or callstatus='Resolved')and datediff(month,convert(datetime,RecvdDate+SPACE(2)+RecvdTime,20),getdate())<=6) as ClosedCalls,
(select 'Assisted Collection' from config where CPDB_View.pupsn=custid and configtype='WM-Assisted') as AssistedCollection,
(select 'Black Sacks' from config where CPDB_View.pupsn=custid and configtype='WM-Black Sacks') as BlackSacks,
(select 'Clinical' from config where CPDB_View.pupsn=custid and configtype='WM-Clinical') as Clinical,
(select 'Cesspit' from config where CPDB_View.pupsn=custid and configtype='WM-Cesspit') as Cesspit,
SUBSTRING(dbo.CPDB_View.puprn, 1, 6) as puprnstring1,
SUBSTRING(dbo.CPDB_View.puprn, 7, 6) as puprnstring2,
dbo.wm_rounds.CollectionType,
dbo.wm_recycleschedule.RecyclingType
FROM dbo.CPDB_View
LEFT OUTER JOIN
dbo.wm_rounds ON
dbo.CPDB_View.Pupsn = dbo.wm_rounds.pupsn
LEFT OUTER JOIN
dbo.wm_recycleschedule ON
DATEPART(week, GETDATE()) = dbo.wm_recycleschedule.WeekNumber
WHEREdbo.CPDB_View.pcurec like 'Y'
Regards
Andy
October 9, 2002 at 8:37 am
Just add a TOP 150. Either to the select-statement calling the view, or in the select-statement that is the view.
SELECT TOP 150 * FROM CPDB
or
Create View CPDB
AS
SELECT TOP 150
dbo.CPDB_View.pupsn .....
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 10/09/2002 08:37:54 AM
October 9, 2002 at 5:35 pm
That works good. An added benefit (possibly) is that by using a TOP you can also use an order by - normally you cannot.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply