Sort view with no control over select statement 1

[jenlion]

I have a view that combines shipping information. Worldship calls this view through a map set up in its own program.

My customer requires that the shipments import in a specific order. To do this, I set up the view to "select top 100 percent" ... "order by salesorderno, itemid".

This used to work, until the day it didn't. I've read both that the "cheat" to sort views works, and that it doesn't. Looks to me like it doesn't!

ANY suggestions? I don't get to write the sql statement WorldShip uses, and there doesn't appear to be any way to got WS to sort unless I export to a text file and import from there, yuck. I'd rather have live data. It's looking impossible, though. Anyone here have a brilliant plan that'll save my butt again????

 

[gmmastros]

You can't change the query that calls the view. I don't like it, but I can understand it. Can you post what the query looks like? If you're not sure, you could run SQL Profiler to capture the query.

-George

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

 

[markros]

The ORDER BY clause doesn't work in the View and this is by design.

See

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

 

[jenlion]

Aha, but there's a new workaround... 100 percent doesn't work, but top 2147483647 does!

Yes, workarounds stink, but my alternatives are worse. When this breaks again, the world doesn't end -- things are just inconvenient. So, having a workaround that will probably work for a really long time (and won't break anybody if it fails) is much better than a tougher process for the end user that's perfect. Everything's a trade off. (And the day they try to ship 2147483647 things at once is the day WorldShip goes down in flames, anyway).

Thanks for the link!

Gmmastros, WorldShip builds the query. It just reads "SELECT ItemID, CustPONo, blah, blah FROM myview". You get to map your view fields to their UPS fields -- "My sales order field is reference 1; my AddressLine1 field is their Address1" -- but that's it! Given that this customer is processing hundreds of orders at a time and has to collate the shipments with paperwork from another place, getting them to print in the same order is quite important. And they don't want to go to another shipping software.

At least know I understand why this worked in SQL 2000 and then stopped in 2005. I live in Redmond but sometimes I wanna throttle MS.

 

[gmmastros]

I'm glad you found another workaround. I had thought of one, but this is a lot better than what I was thinking.

My idea was to create a function that returns the same data as the view, but has an order by clause. Then, create a view that selects from the function.

In your case, you would rename the MyView view to something else, call it.... MyViewUnsorted. Then create a function that selects from MyViewUnsorted but with an order by clause. Then create a view named MyView that selects from the function. You would now have a view (named the same as the original) that returns your sorted data.

But.... I'm glad you don't have to use my ugly hack.

-George

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

 

[Emtucifor]

You might want to look into if you can specify a function name instead of a view name, too, and skip a step.

 

[jenlion]

UPS seems to let you choose views or tables, I think. I'm not very familiar with SQL functions -- I'll add that to my list of things to pay more attention to when the opportunity arises. Thanks for the help!