sql QUERY ANALYZER - SELECT TOP 100 PERCENT

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.
HNA071252Asked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
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
 
HNA071252Author Commented:
I don't get it, can you please post the whole block of code that you think it will work?
0
 
ralmadaCommented:
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
 
HNA071252Author Commented:
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
 
ralmadaCommented:
#a38297744 is not a solution. I've posted a valid solution in #a38297429
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.