FoxPro Help and Support - set exact for remote data source

Author

set exact for remote data source

ghj

2005-06-09, 8:25 pm

Is there a way to set the EXACT setting for remote sql pass-through
commands.

My problem is with the command

SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 <= ('B')

(SQL SERVER 2000 - database field defined as mytable.cfield1 char(20). VFP
9.0)

I would like to retrieve all records that cfield1 starts with either 'A' or
'B'. Now it seems to pad the parameters to the length of 'cfield1' and I do
not get back any records.

Is this an ANSI or other SQL global setting ( I hope)

Any help appreciated.
Thanks in advance.

Jack Jackson

2005-06-09, 8:25 pm

On Fri, 10 Jun 2005 00:07:14 GMT, "ghj" <ghj@ghj.com> wrote:

>Is there a way to set the EXACT setting for remote sql pass-through
>commands.
>
>My problem is with the command
>
>
>SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 <= ('B')
>
>
>(SQL SERVER 2000 - database field defined as mytable.cfield1 char(20). VFP
>9.0)
>
>I would like to retrieve all records that cfield1 starts with either 'A' or
>'B'. Now it seems to pad the parameters to the length of 'cfield1' and I do
>not get back any records.
>
>Is this an ANSI or other SQL global setting ( I hope)

I don't think there is any way to get SQL Server to do what you want
with that SQL Statement.

Some other possibilities for the WHERE clause are:

SUBSTRING(cfield1, 1, 1) >= 'A' and SUBSTRING(cfield1, 1, 1) <= 'B'

SUBSTRING(cfield1, 1, 1) IN ('A', 'B')

SUBSTRING(cfield1, 1, 1) BETWEEN 'A' AND 'B'

cfield1 >= 'A' and cfield1 <= 'B~' -- This may not be safe with
different character sets

Fred Taylor

2005-06-10, 3:24 am

SQL Server doesn't have anything like that. What you can do is a SELECT
like this:

SELECT * FROM mytable WHERE (cField LIKE 'A%' OR cField LIKE 'B%')

This will return all records that begin with "A" or "B".

--
Fred
Microsoft Visual FoxPro MVP

"ghj" <ghj@ghj.com> wrote in message news:SQ4qe.6748$wL2.6552@trndny07...
> Is there a way to set the EXACT setting for remote sql pass-through
> commands.
>
> My problem is with the command
>
>
> SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 <= ('B')
>
>
> (SQL SERVER 2000 - database field defined as mytable.cfield1 char(20).
> VFP 9.0)
>
> I would like to retrieve all records that cfield1 starts with either 'A'
> or 'B'. Now it seems to pad the parameters to the length of 'cfield1' and
> I do not get back any records.
>
> Is this an ANSI or other SQL global setting ( I hope)
>
> Any help appreciated.
> Thanks in advance.
>

Jeroen van Kalken

2005-06-10, 7:24 am

On Fri, 10 Jun 2005 00:07:14 GMT, "ghj" <ghj@ghj.com> wrote:

>Is there a way to set the EXACT setting for remote sql pass-through
>commands.
>
>My problem is with the command
>
>
>SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 <= ('B')
>
>
>(SQL SERVER 2000 - database field defined as mytable.cfield1 char(20). VFP
>9.0)
>
>I would like to retrieve all records that cfield1 starts with either 'A' or
>'B'. Now it seems to pad the parameters to the length of 'cfield1' and I do
>not get back any records.

The easy option is to not use <=B but use <C like:
SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 < ('B')

Or you can add some zzz (or any other 'high'character) to B like in:
SELECT * FROM mytable WHERE cfield1 >= ('A') and cfield1 <= ('Bzzzzz')

Also check out the between function:
SELECT * FROM mytable WHERE cfield1 between ('A') and ('Bzzzz')

Sponsored Links