SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select TOP in VIEW
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JeffT
Posting Yak Master

USA
111 Posts

Posted - 09/11/2006 :  08:49:58  Show Profile  Reply with Quote
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

Sweden
30187 Posts

Posted - 09/11/2006 :  08:56:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
111 Posts

Posted - 09/11/2006 :  09:17:04  Show Profile  Reply with Quote
Problem solved, Thanks very much Peter !!!
Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2006 :  09:37:24  Show Profile  Reply with Quote
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)

Singapore
17595 Posts

Posted - 09/11/2006 :  11:24:49  Show Profile  Reply with Quote
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 - 09/11/2006 :  11:58:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/11/2006 :  15:49:22  Show Profile  Reply with Quote
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

USA
111 Posts

Posted - 09/12/2006 :  08:22:09  Show Profile  Reply with Quote
Just checking back. Thanks to everyone for the optimization tips, I appreciate it !
J
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000