Board index » delphi » Help with local SQL, please?

Help with local SQL, please?

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
--------------------------

 

Re:Help with local SQL, please?


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

Quote
<Harry_Dolder...@csi.com> wrote:
>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??

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

Other Threads