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.
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,JeffHere's what I tried:CREATE VIEW vwCSTProvQueryASdeclare @count intselect @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 Bwhere 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 Bwhere a.custno = b.custnoand Map_Tran in ('837', '835', '820', '834')and a.custno <> 'DMA7384'order by a.idcode, a.GSID, b.Map_tran, b.VersPeter LarssonHelsingborg, Sweden |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-09-11 : 09:17:04
|
Problem solved, Thanks very much Peter !!! Jeff |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
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 spaces2. Use ANSI Joins3. Don't use ORDER BY in Views4. 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 AINNER JOIN TradStat as B ON a.custno = b.custno WHERE Map_Tran in ('837', '835', '820', '834') AND a.custno <> 'DMA7384' Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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. |
|
|
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 |
|
|
|
|
|
|
|