Hi Folks,

I need a query to filter out a list of codes out of a larger set.

I've got the query below, that - so far - works like a charm (in Local SQL,
BDE):

SELECT DISTINCT PositionCode from 'MCBW6.DB' INPUTFILE
WHERE (INPUTFILE.PositionCode >= '11.01')
  AND (INPUTFILE.PositionCode <= '99.99')
ORDER BY INPUTFILE.PositionCode

Now I would like to insert the command SUBSTRING(PositionCode FROM 1 FOR 5)
into the first line, so that the query will list only unique PositionCodes,
based on the first 5 characters of the code.
But then I do that, I'm getting the errormessage that the field PositionCode
then suddenly is NOT FOUND??

What can I be overlooking??

Looking forward to your suggestions!  ;-)

Best regards,
Harry D.

-------
Harry Doldersum,
main e-mail address: i...@Doldersum.com
website: http://www.Doldersum.com
Software for flexible management of your music collection
--------------------------

On Thu, 11 Nov 1999 20:04:43 +0100, "Harry Doldersum"

Any column cited in the ORDER BY clause must also be in the SELECT clause.
Once you change the reference in the SELECT clause to the PositionCode
column to a calculated expression, the PositionCode column cannot be
referenced in the ORDER BY clause. You also cannot use a calculated
expression in an ORDER BY clause.

The answer to this situation is to use a number in the ORDER BY clause
reflecting the realtive position of the calculated value column in the
result set.

  SELECT DISTINCT SUBSTRING(PositionCode FROM 1 FOR 5)
  FROM 'MCBW6.DB' INPUTFILE
  WHERE (INPUTFILE.PositionCode BETWEEN "11.01" AND "99.99")
  ORDER BY 1

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi