Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql QUERY ANALYZER - SELECT TOP 100 PERCENT

Posted on 2012-08-15
7
Medium Priority
?
804 Views
Last Modified: 2012-08-18
Hi,

Can you please tell me why this code does not work?

create view TRP.FFS_Contracts_CTR
SELECT     TOP 100 PERCENT
                          (SELECT     TOP 1 FFSContr_ID
                            FROM          TRP.FFS_Contracts AS B
                            WHERE      (A.Par_id = Par_id) AND (A.TypeOfContract = TypeOfContract) AND (A.Trend_type = Trend_type) AND (A.Year = Year)
                            ORDER BY ActEffDt DESC) AS FFSContr_ID,
                          (SELECT     TOP 1 DealStatus
                            FROM          TRP.FFS_Contracts AS B
                            WHERE      (A.Par_id = Par_id) AND (A.TypeOfContract = TypeOfContract) AND (A.Trend_type = Trend_type) AND (A.Year = Year)
                            ORDER BY ActEffDt DESC) AS DealStatus, Par_id, TypeOfContract, Trend_type, Year, COUNT(ActEffDt) AS CountOfActEffDt, MAX(ActEffDt)
                      AS MaxOfActEffDt
FROM         TRP.FFS_Contracts AS A
WHERE     (Trend_type = 'CTR') AND (TypeOfContract IN ('MDCR', 'MPPO', 'MDCR-O', 'MPPO-O', 'MDCR-A', 'MPPO-A'))
GROUP BY Par_id, TypeOfContract, Trend_type, Year
ORDER BY Par_id, TypeOfContract, Trend_type, Year

I can run the select query and it will give me the result, but when I create a view from this select query it give me an error. Please let me know how to make it work.
0
Comment
Question by:HNA071252
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38297429
you use the order by outside of the view creation, not in it

so

create view TRP.FFS_Contracts_CTR
as
SELECT     
                          (SELECT     TOP 1 FFSContr_ID
                            FROM          TRP.FFS_Contracts AS B
                            WHERE      (A.Par_id = Par_id) AND (A.TypeOfContract = TypeOfContract) AND (A.Trend_type = Trend_type) AND (A.Year = Year)
                            ORDER BY ActEffDt DESC) AS FFSContr_ID,
                          (SELECT     TOP 1 DealStatus
                            FROM          TRP.FFS_Contracts AS B
                            WHERE      (A.Par_id = Par_id) AND (A.TypeOfContract = TypeOfContract) AND (A.Trend_type = Trend_type) AND (A.Year = Year)
                            ORDER BY ActEffDt DESC) AS DealStatus, Par_id, TypeOfContract, Trend_type, Year, COUNT(ActEffDt) AS CountOfActEffDt, MAX(ActEffDt) 
                      AS MaxOfActEffDt
FROM         TRP.FFS_Contracts AS A
WHERE     (Trend_type = 'CTR') AND (TypeOfContract IN ('MDCR', 'MPPO', 'MDCR-O', 'MPPO-O', 'MDCR-A', 'MPPO-A'))
GROUP BY Par_id, TypeOfContract, Trend_type, Year

Open in new window


then

select * from TRP.FFS_Contracts_CTR order by Par_id, TypeOfContract, Trend_type, Year
0
 

Author Comment

by:HNA071252
ID: 38297744
I don't get it, can you please post the whole block of code that you think it will work?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38297766
I've already posted it above. Both the view creation and how to use the view in a query.
Notice that I've dropped the ORDER BY and TOP 100 PERCENT in the view creation script.
0
 

Author Comment

by:HNA071252
ID: 38297862
I've requested that this question be closed as follows:

Accepted answer: 0 points for HNA071252's comment #a38297744

for the following reason:

I figured the error myself.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38297863
#a38297744 is not a solution. I've posted a valid solution in #a38297429
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question