Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select TOP in VIEW

Author  Topic 

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-11 : 08:49:58
Hi,

I am creating a VIEW with an ORDER BY clause. When I do a straight SELECT it tells me I can only use ORDER BY in a VIEW with TOP specified. I want all rows in the Table to be selected so I tried to get the count of the table and feed it to the SELECT TOP like this (see query below) but I get an "Incorrect syntax near '@count'" error.
Any ideas ?
Thanks,
Jeff

Here's what I tried:

CREATE VIEW vwCSTProvQuery
AS
declare @count int
select @count = (select count(*) from TP)
Select TOP @count a.IDQual as 'ISA Qual', a.IDCODE as 'ISA No', a.GSID as 'GS ID', a.Name,
CASE
WHEN b.Vers LIKE '004010X096%' THEN '837I'
WHEN b.Vers LIKE '004010X097%' THEN '837D'
WHEN b.Vers LIKE '004010X098%' THEN '837P'
WHEN b.Vers LIKE '004010X091%' THEN '835'
WHEN b.Vers LIKE '004010X061%' THEN '820'
WHEN b.Vers LIKE '004010X095%' THEN '834'
ELSE 'N/A'
END as 'Tran Type'
From TP as A,
TradStat as B
where a.custno = b.custno
and Map_Tran in ('837', '835', '820', '834')
and a.custno <> 'DMA7384'
order by a.idcode, a.GSID, b.Map_tran, b.Vers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 08:56:48
Use TOP 100 PERCENT...


Select TOP 100 percent a.IDQual as 'ISA Qual', a.IDCODE as 'ISA No', a.GSID as 'GS ID', a.Name,
CASE
WHEN b.Vers LIKE '004010X096%' THEN '837I'
WHEN b.Vers LIKE '004010X097%' THEN '837D'
WHEN b.Vers LIKE '004010X098%' THEN '837P'
WHEN b.Vers LIKE '004010X091%' THEN '835'
WHEN b.Vers LIKE '004010X061%' THEN '820'
WHEN b.Vers LIKE '004010X095%' THEN '834'
ELSE 'N/A'
END as 'Tran Type'
From TP as A,
TradStat as B
where a.custno = b.custno
and Map_Tran in ('837', '835', '820', '834')
and a.custno <> 'DMA7384'
order by a.idcode, a.GSID, b.Map_tran, b.Vers


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-11 : 09:17:04
Problem solved, Thanks very much Peter !!!
Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-11 : 09:37:24
Why do you want to have it Ordered?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-11 : 11:24:49
just specify the ordering when you are selecting from the view. You don't have to have the view ordered.

select * from vwCSTProvQuery order by idcode, GSID, Map_tran, Vers



KH

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-11 : 11:58:00
Couple of other things.

1. Don't use Column names with spaces
2. Use ANSI Joins
3. Don't use ORDER BY in Views
4. Format your code so it's more readable



SELECT a.IDQual as ISA_Qual
, a.IDCODE as ISA_No
, a.GSID as GS_ID
, a.Name
, CASE
WHEN b.Vers LIKE '004010X096%' THEN '837I'
WHEN b.Vers LIKE '004010X097%' THEN '837D'
WHEN b.Vers LIKE '004010X098%' THEN '837P'
WHEN b.Vers LIKE '004010X091%' THEN '835'
WHEN b.Vers LIKE '004010X061%' THEN '820'
WHEN b.Vers LIKE '004010X095%' THEN '834'
ELSE 'N/A'
END as Tran_Type
FROM TP as A
INNER JOIN TradStat as B
ON a.custno = b.custno
WHERE Map_Tran in ('837', '835', '820', '834')
AND a.custno <> 'DMA7384'




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-11 : 15:49:22
quote:
3. Don't use ORDER BY in Views

JeffT - just to explain here, if you force SQL Server to order the rows in the view, it is very inefficient to use that view with another ordering. It always has to order according to the view first, even if you then query the view in a different order.

Also, even when you don't specify a different ordering when you use the view, you still severely limit the optimizer when it tries to optimize queries that use the view.

Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-12 : 08:22:09
Just checking back. Thanks to everyone for the optimization tips, I appreciate it !
J
Go to Top of Page
   

- Advertisement -