Edited by Lewis Mccabe, 17 October 2012 - 03:37 PM.
MS SQL Server - TOP clause fails
Posted 17 October 2012 - 03:36 PM
Posted 18 October 2012 - 08:02 AM
Can you change it to a Type-1 SELECT? That might sidestep your key-field retrieval problem too.
Posted 18 November 2012 - 03:51 PM
You are correct that we can only use a type 1 select with primary file in change mode. That was the issue. We are vehimently trying to avoid type 2 statements. We sacrifice performance for maintability (up to a point). Both these issues require a type 2.
So for all you researching this down the road:
This pertains to SQL Server but as Richard points out, it probably is the same in all SQL flavors
1. You cannot put the primary file in change mode if you are employing a type 2 statement for selection of the primary file. Put the primary file in lookup and change the data in a secondary file.
2. If you need to employ the TOP clause (which we do extensively because of the huge performance improvements) you have to use a type 2 statement.
Edited by Lewis Mccabe, 18 November 2012 - 03:52 PM.
Posted 18 November 2012 - 04:34 PM
Bizarre we're both here at the same time a month later!
Interestingly, you don't need a Type-2 statement to use the equivalent of TOP in Oracle (althought you do have to jump through a lot of nasty hoops to make it work properly). I guess the problem with TOP is it has to follow the SELECT keyword in the SQL/Server syntax.
Is it possible you could sidestep this annoyance by capturing the TOP within a create-view and querying the resulting view with a Type-1? Such a view might even be updatable?
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users