View Ordering Not Working 1

[Aidy680]

Does anyone know why my view is completely ignoring my ORDER clause?

To make it more confusing, when I have it open in DESIGN view and execute it, it works fine. When I open it, without first viewing it in design view, the results are unordered. Crazy!!

The SQL is below:

SELECT TOP (100) PERCENT DateFirstAppeared, SEDOLFODB, HeaderDescriptionFODB, BaseFundCodeFODB, NotionalFODB, SEDOLSystem,
BaseFundCodeSystem, NotionalSystem, RunNumber, Comments, Level1Authorisation, Level2Authorisation, Level1SignedOffDate,
Level2SignedOffDate, SignOffFlag, KnownExceptionFlag, FinalExceptionsHistoryFlag
FROM dbo.vwView1 AS A
WHERE (NOT EXISTS
(SELECT DateFirstAppeared, SEDOLFODB, HeaderDescriptionFODB, BaseFundCodeFODB, NotionalFODB, SEDOLSystem, BaseFundCodeSystem,
NotionalSystem, RunNumber, Comments, Level1Authorisation, Level2Authorisation, Level1SignedOffDate, Level2SignedOffDate,
SignOffFlag, KnownExceptionFlag, 'FALSE' AS FinalExceptionsHistoryFlag
FROM dbo.vwFinalExceptionsVrsExceptionsSystem1 AS B
WHERE (A.DateFirstAppeared = DateFirstAppeared) AND (A.SEDOLFODB = SEDOLFODB) AND (HeaderDescriptionFODB = HeaderDescriptionFODB)
AND (A.BaseFundCodeFODB = BaseFundCodeFODB) AND (A.NotionalFODB = NotionalFODB) AND (A.SEDOLSystem = SEDOLSystem) AND
(A.BaseFundCodeSystem = BaseFundCodeSystem) AND (A.NotionalSystem = NotionalSystem) AND (A.RunNumber = RunNumber) AND
(A.Comments = Comments) AND (A.Level1Authorisation = Level1Authorisation) AND (A.Level2Authorisation = Level2Authorisation) AND
(A.Level1SignedOffDate = Level1SignedOffDate) AND (A.Level2SignedOffDate = Level2SignedOffDate) AND (A.SignOffFlag = SignOffFlag)
AND (A.KnownExceptionFlag = KnownExceptionFlag) AND (A.FinalExceptionsHistoryFlag = FinalExceptionsHistoryFlag)))

ORDER BY Level1Authorisation DESC, Level2Authorisation DESC

 

[gmmastros]

You should not rely on ordering within the view. Instead, you should order the rows when you USE the view. Ex:

Select * From MyViewName Order By Level1Authorisation DESC, Level2Authorisation DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Aidy680]

Thanks for your speedy reply but I'm afraid that still doesn't work.

I was using a proc to select the results from my view but I cant do that anymore due to connection issues to my Access FE.

As I say, the confusing thing is that in design view the results are ordered; but not if I right-click on the view and select 'Open View'.

 

[markros]

It is by design. See

http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

and

http://social.msdn.microsoft.com/Fo...l/thread/ff1ee747-41d0-4664-8114-d0eaa9fea53f

with this link explaining it

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx

 

[Aidy680]

Thanks Markros. Looks like there is a workaround in there which I'll give a go tomorrow.

I'll let you know how it goes.

 

[gmmastros]

Aidy680

The workaround used to be: Top 100 Percent. That workaround no longer works (as you can see). I would caution you against using another workaround. Who knows how long this one will last?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Aidy680]

Works OK on a single query but not a UNION, so will have to link it into my Access FE and create a local query on that and do the ordering in there.

I'm close to giving up on IT - nothing is ever easy....!!

Thanks for all your help guys.

 

[Aidy680]

...or you can create another view on the UNION, which you can order and link to that!!

 

[Aidy680]

OK I've got my ordering working but now I have an issue with NULLS and the NOT EXISTS clause.

Basically, taking the SQL at the top of the post, if any of the values in the fields are NULL, then the query returns those records as well, when they should be filtered out.

It's as if it cant compare 2 NULLS. I have read that SQL does behave like this.

Is there a workaround?

 

[Aidy680]

If anyone is interested...

I used the IFNULL function in the WHERE clause to get round this problem.

eg.

IFNULL(A.FIELD, 'TRUE') = (IFNULL(FIELD, 'TRUE')

God it's quiet round here today!!

 

[markros]

What is IFNULL? Did you mean ISNULL ?

 

[Aidy680]

No I mean IFNULL(). It's the same as ISNULL I think.

 

[r937]

there is no IFNULL function

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Aidy680]

There is. I typed IFNULL into a View design pane and it works. When I save it, it seems to prefix it automatically with a 'fn{}' construct, which I'm not entirely sure about admitedly, but it works.

Have you tried it?

 

[r937]

there is no IFNULL function in SQL Server

i checked da manual on da microsoft web site

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Are you developing a report?

 

[markros]

Actually, I searched SSRS and it's not there either. Looks like we were talking about MySQL

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

- but then the link I provided may be irrelevant.

Found also this interesting blog

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx

 

[r937]

Looks like we were talking about MySQL

no, mysql would never allow an absurdity like "TOP 100 PERCENT"



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

You mean.... there's nothing absurd about MySQL???

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Aidy680]

gmmastros your links were very helpful with regard to the initial ordering issue.

I'll leave it to others to try using IFNULL in the design pane of a view to see if it works...which it does.

Whether you can save it and run it is a different question though....