Jump to content


Photo
- - - - -

MS SQL Server - TOP clause fails


4 replies to this topic

#1 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 17 October 2012 - 03:36 PM

Has anyone been able to wrap up the TOP command in SQL/ENDSQL and get it to work? So far we have two issues. The first is when using TOP in selection of records in an update (cycle entry logic), the primary file cannot be in change, otherwise SQL Server complains and we get Error 366. We have to put primary in lookup and have secondary file of the same name in change. Secondly, we found the data which was being returned was corrupted. One of our key fields of the primary file was getting its first character removed. In both cases we want to return only 1 record.

Edited by Lewis Mccabe, 17 October 2012 - 03:37 PM.


#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 18 October 2012 - 08:02 AM

Never used SQL/Server, however, I think it would be standard that you cannot use anything other than lookup mode with a Type-2 SELECT if that's what you are using.

Can you change it to a Type-1 SELECT? That might sidestep your key-field retrieval problem too.
Nothing's as simple as you think

#3 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 18 November 2012 - 03:51 PM

Richard,


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.


#4 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 18 November 2012 - 04:34 PM

Hi Lewis,

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?
Nothing's as simple as you think

#5 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 19 November 2012 - 10:27 PM

Hi Richard,

Bizarre indeed.


I will check it out when I get some time. It sounds as if it should work without a hitch.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users