SELECT - SQL result set dependant on whether table is indexed 
Author Message
 SELECT - SQL result set dependant on whether table is indexed

Any help with the following would be much appreciated!

The following program uses SELECT - SQL to extract records from cursor
a, based on the contents of a matching field in cursor b (the zipcode).

In VPF 5.0a the code behaves differently depending on whether the
zipcode field is indexed. If it is NOT indexed, the behavior is as
expected - all 3 records with zipcode='021' are returned. If the field
IS indexed, no records are returned.

Running the exact same prg in FPW 2.6a gives the expected result (3
records returned), whether or not the field is indexed.

Is this a bug, or am I doing something wrong? If it is a bug, any
suggestions for a workaround?

I'm running NT4.0, if that makes any difference.

Thanks!

John Howard

**********************
procedure testproc
close all

create cursor a (city c(40), zipcode c(5))
insert into a values ('BOSTON','02115')
insert into a values ('BOSTON','02118')
insert into a values ('CAMBRIDGE','02138')

create cursor b (zipcode c(5))
insert into b values ('021')

select a.city, a.zipcode ;
from a, b ;
where a.zipcode=alltrim(b.zipcode) ;
into cursor x
* cursor x has three records

select a
index on zipcode tag zipcode

select a.city, a.zipcode ;
from a, b ;
where a.zipcode=alltrim(b.zipcode) ;
into cursor xx
* cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

return
**********************



Fri, 25 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
Hi John,

under which collate sequence is your index build (check out idxcollate()
!! )
and under which is your query running ( checkout set("collate") !! )
Make sure that they are the same and named 'machine' <s>

Hope that helps.

--
Holger Vorberg

 MS Visual FoxPro MVP
 dFPUG Regionalleiter Bielefeld
-------------------------------------- |\_/|
   Prolib Software GmbH, www.prolib.de (.. )
   Wir machen dem Fuchs Beine <g>       - /


Quote:
>Any help with the following would be much appreciated!

>The following program uses SELECT - SQL to extract records from cursor
>a, based on the contents of a matching field in cursor b (the zipcode).

>In VPF 5.0a the code behaves differently depending on whether the
>zipcode field is indexed. If it is NOT indexed, the behavior is as
>expected - all 3 records with zipcode='021' are returned. If the field
>IS indexed, no records are returned.

>Running the exact same prg in FPW 2.6a gives the expected result (3
>records returned), whether or not the field is indexed.

>Is this a bug, or am I doing something wrong? If it is a bug, any
>suggestions for a workaround?

>I'm running NT4.0, if that makes any difference.

>Thanks!

>John Howard

>**********************
>procedure testproc
>close all

>create cursor a (city c(40), zipcode c(5))
>insert into a values ('BOSTON','02115')
>insert into a values ('BOSTON','02118')
>insert into a values ('CAMBRIDGE','02138')

>create cursor b (zipcode c(5))
>insert into b values ('021')

>select a.city, a.zipcode ;
>from a, b ;
>where a.zipcode=alltrim(b.zipcode) ;
>into cursor x
>* cursor x has three records

>select a
>index on zipcode tag zipcode

>select a.city, a.zipcode ;
>from a, b ;
>where a.zipcode=alltrim(b.zipcode) ;
>into cursor xx
>* cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

>return
>**********************



Sat, 26 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
Hi John,

In addition look IDXCOLLATE() and REINDEX

Best regards,
Michael , Russia
___________________________________________________________________________

ICS page: http://www.ics.perm.ru/ [in Russian]
My page: http://skyscraper.fortunecity.com/zeros/287/
__________________________________________________________________________

Quote:
> Any help with the following would be much appreciated!

> The following program uses SELECT - SQL to extract records from cursor
> a, based on the contents of a matching field in cursor b (the zipcode).

> In VPF 5.0a the code behaves differently depending on whether the
> zipcode field is indexed. If it is NOT indexed, the behavior is as
> expected - all 3 records with zipcode='021' are returned. If the field
> IS indexed, no records are returned.

> Running the exact same prg in FPW 2.6a gives the expected result (3
> records returned), whether or not the field is indexed.

> Is this a bug, or am I doing something wrong? If it is a bug, any
> suggestions for a workaround?

> I'm running NT4.0, if that makes any difference.

> Thanks!

> John Howard

> **********************
> procedure testproc
> close all

> create cursor a (city c(40), zipcode c(5))
> insert into a values ('BOSTON','02115')
> insert into a values ('BOSTON','02118')
> insert into a values ('CAMBRIDGE','02138')

> create cursor b (zipcode c(5))
> insert into b values ('021')

> select a.city, a.zipcode ;
> from a, b ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor x
> * cursor x has three records

> select a
> index on zipcode tag zipcode

> select a.city, a.zipcode ;
> from a, b ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor xx
> * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

> return
> **********************



Sat, 26 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
John
I get the same results using your code, and VFP 6.0 SP 3. Changing the
cursors to table helps
though
create table aa (city c(40), zipcode c(5))
insert into aa values ('BOSTON','02115')
insert into aa values ('BOSTON','02118')
insert into aa values ('CAMBRIDGE','02138')

create table bb (zipcode c(5))
insert into bb values ('021')

select AA.city, AA.zipcode ;
from aa join bb ;
on AA.zipcode=alltrim(bb.zipcode) nofilter ;
into cursor x
* cursor x has three records

select aa
*index on zipcode tag zipcode

select AA.city, AA.zipcode ;
from aa join bb ;
on AA.zipcode=alltrim(bb.zipcode) nofilter ;
into cursor xx
*
-Anders


| Any help with the following would be much appreciated!
|
| The following program uses SELECT - SQL to extract records from cursor
| a, based on the contents of a matching field in cursor b (the zipcode).
|
| In VPF 5.0a the code behaves differently depending on whether the
| zipcode field is indexed. If it is NOT indexed, the behavior is as
| expected - all 3 records with zipcode='021' are returned. If the field
| IS indexed, no records are returned.
|
| Running the exact same prg in FPW 2.6a gives the expected result (3
| records returned), whether or not the field is indexed.
|
| Is this a bug, or am I doing something wrong? If it is a bug, any
| suggestions for a workaround?
|
| I'm running NT4.0, if that makes any difference.
|
| Thanks!
|
| John Howard
|
| **********************
| procedure testproc
| close all
|
| create cursor a (city c(40), zipcode c(5))
| insert into a values ('BOSTON','02115')
| insert into a values ('BOSTON','02118')
| insert into a values ('CAMBRIDGE','02138')
|
| create cursor b (zipcode c(5))
| insert into b values ('021')
|
| select a.city, a.zipcode ;
| from a, b ;
| where a.zipcode=alltrim(b.zipcode) ;
| into cursor x
| * cursor x has three records
|
| select a
| index on zipcode tag zipcode
|
| select a.city, a.zipcode ;
| from a, b ;
| where a.zipcode=alltrim(b.zipcode) ;
| into cursor xx
| * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)
|
| return
| **********************



Mon, 28 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
Reading your code, my gues is that you want to select all the values in a
lookup-table that are the same in start as the selected value.
In that case you don't have to add the tablename of the selection-createria
in the from-statement.
That way, your problem is also solved.

There is some explanation possible, and to make it visual, i used the
command
SYS(3054,11). This enables display of Rushmore optimization levels for
queries. to disable it use SYS(3054,0).
Place SYS(3054,11) before the 1st selection, and look at the output on your
screen. With the b-cursor in your from-clause an indication is given that a
join is made
Output is something like:

Quote:
>Rushmore optimalisation level for intermediate result: none
>Rushmore optimalisation level for intermediate result: none
>Joining intermediate result and intermediate result using temp index

The result is three records
Quote:
>Rushmore optimalisation level for intermediate result: none
>Rushmore optimalisation level for intermediate result: none
>Joining intermediate result and intermediate result using index tag Zipcode

The result is no records

If you remove the b-cursor from the from-clause in your select-statement
jou'll get output like this:

Quote:
>Rushmore optimalisation level for intermediate result: none

The result is three records
Quote:
>Using index tag Zipcode to rushmore optimize intermediate result
>Rushmore optimalisation level for intermediate result: full

The result is three records
(p.s. i used a SET DELETED OFF before running the program, otherwise the
optimalisation is partial)

Further testing gives the indication that when the use of an index is
supressed in the 2nd select, the results are correct (three records). This
can be done by ;
1) modifying the index-statement into 'index on alltrim(zipcode) tag
zipcode', or
2)by using FORCE in the select :

Quote:
> select a.city, a.zipcode ;
> from a, b force ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor xx

The last indicates with the SYS(3054,11) statement, that a join using a
Cartesian product is created.

But when you use the two given options the select is not optimized, which
was the whole meaning of the index-clause, right??
To optimize the select, remove the 2nd cursor from the source:

Quote:
> select a.city, a.zipcode ;
> from a ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor xx

The query is fully optimized(if set deleted is off or an index is also added
on deleted() )

Hope this helps you,

--
Sietse Wijnker
Bizzlink Net Services,
Holland.


Quote:
> Any help with the following would be much appreciated!

> The following program uses SELECT - SQL to extract records from cursor
> a, based on the contents of a matching field in cursor b (the zipcode).

> In VPF 5.0a the code behaves differently depending on whether the
> zipcode field is indexed. If it is NOT indexed, the behavior is as
> expected - all 3 records with zipcode='021' are returned. If the field
> IS indexed, no records are returned.

> Running the exact same prg in FPW 2.6a gives the expected result (3
> records returned), whether or not the field is indexed.

> Is this a bug, or am I doing something wrong? If it is a bug, any
> suggestions for a workaround?

> I'm running NT4.0, if that makes any difference.

> Thanks!

> John Howard

> **********************
> procedure testproc
> close all

> create cursor a (city c(40), zipcode c(5))
> insert into a values ('BOSTON','02115')
> insert into a values ('BOSTON','02118')
> insert into a values ('CAMBRIDGE','02138')

> create cursor b (zipcode c(5))
> insert into b values ('021')

> select a.city, a.zipcode ;
> from a, b ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor x
> * cursor x has three records

> select a
> index on zipcode tag zipcode

> select a.city, a.zipcode ;
> from a, b ;
> where a.zipcode=alltrim(b.zipcode) ;
> into cursor xx
> * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

> return
> **********************



Mon, 28 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
Sietse,

Thanks for your reply.

What I am trying to do is a bit more complicated. Generally, the cursor
b will have multiple records. The intent is to extract records from
cursor (or table) a which have zipcodes matching *any* of the zipcodes
in b. To do this requires that b be listed in the from-clause.
Otherwise, records are extracted from a for only that zipcode referenced
by the record pointer in b.

It is possible to do the same thing with:

 select a.city, a.zipcode ;
 from a ;
 where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
 into cursor x

or with:

 select a.city, a.zipcode ;
 from a ;
 where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
 into cursor x

but the use of a cursor (b) as a lookup table is much faster and cleaner
- if it only worked!

John Howard

Quote:

> Reading your code, my gues is that you want to select all the values in a
> lookup-table that are the same in start as the selected value.
> In that case you don't have to add the tablename of the selection-createria
> in the from-statement.
> That way, your problem is also solved.

> There is some explanation possible, and to make it visual, i used the
> command
> SYS(3054,11). This enables display of Rushmore optimization levels for
> queries. to disable it use SYS(3054,0).
> Place SYS(3054,11) before the 1st selection, and look at the output on your
> screen. With the b-cursor in your from-clause an indication is given that a
> join is made
> Output is something like:
> >Rushmore optimalisation level for intermediate result: none
> >Rushmore optimalisation level for intermediate result: none
> >Joining intermediate result and intermediate result using temp index
> The result is three records
> >Rushmore optimalisation level for intermediate result: none
> >Rushmore optimalisation level for intermediate result: none
> >Joining intermediate result and intermediate result using index tag Zipcode
> The result is no records

> If you remove the b-cursor from the from-clause in your select-statement
> jou'll get output like this:

> >Rushmore optimalisation level for intermediate result: none
> The result is three records
> >Using index tag Zipcode to rushmore optimize intermediate result
> >Rushmore optimalisation level for intermediate result: full
> The result is three records
> (p.s. i used a SET DELETED OFF before running the program, otherwise the
> optimalisation is partial)

> Further testing gives the indication that when the use of an index is
> supressed in the 2nd select, the results are correct (three records). This
> can be done by ;
> 1) modifying the index-statement into 'index on alltrim(zipcode) tag
> zipcode', or
> 2)by using FORCE in the select :
> > select a.city, a.zipcode ;
> > from a, b force ;
> > where a.zipcode=alltrim(b.zipcode) ;
> > into cursor xx
> The last indicates with the SYS(3054,11) statement, that a join using a
> Cartesian product is created.

> But when you use the two given options the select is not optimized, which
> was the whole meaning of the index-clause, right??
> To optimize the select, remove the 2nd cursor from the source:
> > select a.city, a.zipcode ;
> > from a ;
> > where a.zipcode=alltrim(b.zipcode) ;
> > into cursor xx
> The query is fully optimized(if set deleted is off or an index is also added
> on deleted() )

> Hope this helps you,

> --
> Sietse Wijnker
> Bizzlink Net Services,
> Holland.



> > Any help with the following would be much appreciated!

> > The following program uses SELECT - SQL to extract records from cursor
> > a, based on the contents of a matching field in cursor b (the zipcode).

> > In VPF 5.0a the code behaves differently depending on whether the
> > zipcode field is indexed. If it is NOT indexed, the behavior is as
> > expected - all 3 records with zipcode='021' are returned. If the field
> > IS indexed, no records are returned.

> > Running the exact same prg in FPW 2.6a gives the expected result (3
> > records returned), whether or not the field is indexed.

> > Is this a bug, or am I doing something wrong? If it is a bug, any
> > suggestions for a workaround?

> > I'm running NT4.0, if that makes any difference.

> > Thanks!

> > John Howard

> > **********************
> > procedure testproc
> > close all

> > create cursor a (city c(40), zipcode c(5))
> > insert into a values ('BOSTON','02115')
> > insert into a values ('BOSTON','02118')
> > insert into a values ('CAMBRIDGE','02138')

> > create cursor b (zipcode c(5))
> > insert into b values ('021')

> > select a.city, a.zipcode ;
> > from a, b ;
> > where a.zipcode=alltrim(b.zipcode) ;
> > into cursor x
> > * cursor x has three records

> > select a
> > index on zipcode tag zipcode

> > select a.city, a.zipcode ;
> > from a, b ;
> > where a.zipcode=alltrim(b.zipcode) ;
> > into cursor xx
> > * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

> > return
> > **********************



Thu, 31 Jan 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
John,

These 3 will all work:

*!* (Your choice number 1)
select a.city, a.zipcode ;
 from a ;
 where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
 into cursor x

*!* (Your choice number 2)
select a.city, a.zipcode ;
 from a ;
 where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
 into cursor x

but unless you are looking for only a few specific codes, this will be the
best:
SELECT a.* ;
FROM a ;
WHERE a.Zipcode IN ;
(SELECT Zipcode FROM b)

You should also read about the effect that SET EXACT has on string
comparisons and play with it.  If there are 3 chars on the left and 5 on the
right they may or may not be "equal".  This of course applies only to the
two choices above.

If all of the choices in b have the same number of characters (like you're
looking for all the "021"'s you can use

WHERE SUBSTR(a.Zipcode, x) IN (SELECT SUBSTR(b.Zipcode, x) FROM b)

One more approach - if the b list is relativelly short, loop through b
selecting matching records from a"
SELECT a
=AFIELDS(temparray)
CREATE CURSOR Collection FROM ARRAY Temparray    && Make a cursor to collect
all the ones we picked,  with the same structure as a

SELECT b
SCAN
    MyZip = b.Zipcode
    SELECT * FROM A WHERE a.Zipcode = MyZip INTO CURSOR Tempcurs
    SELECT Collection
    APPEND FROM DBF("Tempcurs")
ENDSCAN

--
Cindy Winegarden

Duke Children's Information Systems
Duke Univeristy Medical Center


Quote:
> Sietse,

> Thanks for your reply.

> What I am trying to do is a bit more complicated. Generally, the cursor
> b will have multiple records. The intent is to extract records from
> cursor (or table) a which have zipcodes matching *any* of the zipcodes
> in b. To do this requires that b be listed in the from-clause.
> Otherwise, records are extracted from a for only that zipcode referenced
> by the record pointer in b.

> It is possible to do the same thing with:

>  select a.city, a.zipcode ;
>  from a ;
>  where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
>  into cursor x

> or with:

>  select a.city, a.zipcode ;
>  from a ;
>  where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
>  into cursor x

> but the use of a cursor (b) as a lookup table is much faster and cleaner
> - if it only worked!

> John Howard


> > Reading your code, my gues is that you want to select all the values in
a
> > lookup-table that are the same in start as the selected value.
> > In that case you don't have to add the tablename of the
selection-createria
> > in the from-statement.
> > That way, your problem is also solved.

> > There is some explanation possible, and to make it visual, i used the
> > command
> > SYS(3054,11). This enables display of Rushmore optimization levels for
> > queries. to disable it use SYS(3054,0).
> > Place SYS(3054,11) before the 1st selection, and look at the output on
your
> > screen. With the b-cursor in your from-clause an indication is given
that a
> > join is made
> > Output is something like:
> > >Rushmore optimalisation level for intermediate result: none
> > >Rushmore optimalisation level for intermediate result: none
> > >Joining intermediate result and intermediate result using temp index
> > The result is three records
> > >Rushmore optimalisation level for intermediate result: none
> > >Rushmore optimalisation level for intermediate result: none
> > >Joining intermediate result and intermediate result using index tag
Zipcode
> > The result is no records

> > If you remove the b-cursor from the from-clause in your select-statement
> > jou'll get output like this:

> > >Rushmore optimalisation level for intermediate result: none
> > The result is three records
> > >Using index tag Zipcode to rushmore optimize intermediate result
> > >Rushmore optimalisation level for intermediate result: full
> > The result is three records
> > (p.s. i used a SET DELETED OFF before running the program, otherwise the
> > optimalisation is partial)

> > Further testing gives the indication that when the use of an index is
> > supressed in the 2nd select, the results are correct (three records).
This
> > can be done by ;
> > 1) modifying the index-statement into 'index on alltrim(zipcode) tag
> > zipcode', or
> > 2)by using FORCE in the select :
> > > select a.city, a.zipcode ;
> > > from a, b force ;
> > > where a.zipcode=alltrim(b.zipcode) ;
> > > into cursor xx
> > The last indicates with the SYS(3054,11) statement, that a join using a
> > Cartesian product is created.

> > But when you use the two given options the select is not optimized,
which
> > was the whole meaning of the index-clause, right??
> > To optimize the select, remove the 2nd cursor from the source:
> > > select a.city, a.zipcode ;
> > > from a ;
> > > where a.zipcode=alltrim(b.zipcode) ;
> > > into cursor xx
> > The query is fully optimized(if set deleted is off or an index is also
added
> > on deleted() )

> > Hope this helps you,

> > --
> > Sietse Wijnker
> > Bizzlink Net Services,
> > Holland.



> > > Any help with the following would be much appreciated!

> > > The following program uses SELECT - SQL to extract records from cursor
> > > a, based on the contents of a matching field in cursor b (the
zipcode).

> > > In VPF 5.0a the code behaves differently depending on whether the
> > > zipcode field is indexed. If it is NOT indexed, the behavior is as
> > > expected - all 3 records with zipcode='021' are returned. If the field
> > > IS indexed, no records are returned.

> > > Running the exact same prg in FPW 2.6a gives the expected result (3
> > > records returned), whether or not the field is indexed.

> > > Is this a bug, or am I doing something wrong? If it is a bug, any
> > > suggestions for a workaround?

> > > I'm running NT4.0, if that makes any difference.

> > > Thanks!

> > > John Howard

> > > **********************
> > > procedure testproc
> > > close all

> > > create cursor a (city c(40), zipcode c(5))
> > > insert into a values ('BOSTON','02115')
> > > insert into a values ('BOSTON','02118')
> > > insert into a values ('CAMBRIDGE','02138')

> > > create cursor b (zipcode c(5))
> > > insert into b values ('021')

> > > select a.city, a.zipcode ;
> > > from a, b ;
> > > where a.zipcode=alltrim(b.zipcode) ;
> > > into cursor x
> > > * cursor x has three records

> > > select a
> > > index on zipcode tag zipcode

> > > select a.city, a.zipcode ;
> > > from a, b ;
> > > where a.zipcode=alltrim(b.zipcode) ;
> > > into cursor xx
> > > * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

> > > return
> > > **********************



Fri, 01 Feb 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
Cindy,

Thanks for your reply.

I did try your solution:

SELECT a.* ;
FROM a ;
WHERE a.Zipcode IN ;
(SELECT Zipcode FROM b)

and in my hands (VFP 5.0a), when b.zipcode contains a 3-digit zipcode,
it does not return any records, even when a is not indexed on zipcode.
It does return the correct record, though, when b.zipcode contains a
5-digit zipcode.

According to the VFP documentation, SET ANSI determines how the "="
operator works in SQL statements, not SET EXACT. I did look into that,
and the setting of exact has no influence, whereas with SET ANSI ON the
query returns 0 records whether or not there is an index on zipcode in
a. With SET ANSI OFF, the program behaves as I have been describing
(result depends on whether there is an index).

So I am still in a bit of a bind, unless there is some other environment
variable that I am not setting properly.

John Howard

Quote:

> John,

> These 3 will all work:

> *!* (Your choice number 1)
> select a.city, a.zipcode ;
>  from a ;
>  where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
>  into cursor x

> *!* (Your choice number 2)
> select a.city, a.zipcode ;
>  from a ;
>  where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
>  into cursor x

> but unless you are looking for only a few specific codes, this will be the
> best:
> SELECT a.* ;
> FROM a ;
> WHERE a.Zipcode IN ;
> (SELECT Zipcode FROM b)

> You should also read about the effect that SET EXACT has on string
> comparisons and play with it.  If there are 3 chars on the left and 5 on the
> right they may or may not be "equal".  This of course applies only to the
> two choices above.

> If all of the choices in b have the same number of characters (like you're
> looking for all the "021"'s you can use

> WHERE SUBSTR(a.Zipcode, x) IN (SELECT SUBSTR(b.Zipcode, x) FROM b)

> One more approach - if the b list is relativelly short, loop through b
> selecting matching records from a"
> SELECT a
> =AFIELDS(temparray)
> CREATE CURSOR Collection FROM ARRAY Temparray    && Make a cursor to collect
> all the ones we picked,  with the same structure as a

> SELECT b
> SCAN
>     MyZip = b.Zipcode
>     SELECT * FROM A WHERE a.Zipcode = MyZip INTO CURSOR Tempcurs
>     SELECT Collection
>     APPEND FROM DBF("Tempcurs")
> ENDSCAN

> --
> Cindy Winegarden

> Duke Children's Information Systems
> Duke Univeristy Medical Center



> > Sietse,

> > Thanks for your reply.

> > What I am trying to do is a bit more complicated. Generally, the cursor
> > b will have multiple records. The intent is to extract records from
> > cursor (or table) a which have zipcodes matching *any* of the zipcodes
> > in b. To do this requires that b be listed in the from-clause.
> > Otherwise, records are extracted from a for only that zipcode referenced
> > by the record pointer in b.

> > It is possible to do the same thing with:

> >  select a.city, a.zipcode ;
> >  from a ;
> >  where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
> >  into cursor x

> > or with:

> >  select a.city, a.zipcode ;
> >  from a ;
> >  where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
> >  into cursor x

> > but the use of a cursor (b) as a lookup table is much faster and cleaner
> > - if it only worked!

> > John Howard


> > > Reading your code, my gues is that you want to select all the values in
> a
> > > lookup-table that are the same in start as the selected value.
> > > In that case you don't have to add the tablename of the
> selection-createria
> > > in the from-statement.
> > > That way, your problem is also solved.

> > > There is some explanation possible, and to make it visual, i used the
> > > command
> > > SYS(3054,11). This enables display of Rushmore optimization levels for
> > > queries. to disable it use SYS(3054,0).
> > > Place SYS(3054,11) before the 1st selection, and look at the output on
> your
> > > screen. With the b-cursor in your from-clause an indication is given
> that a
> > > join is made
> > > Output is something like:
> > > >Rushmore optimalisation level for intermediate result: none
> > > >Rushmore optimalisation level for intermediate result: none
> > > >Joining intermediate result and intermediate result using temp index
> > > The result is three records
> > > >Rushmore optimalisation level for intermediate result: none
> > > >Rushmore optimalisation level for intermediate result: none
> > > >Joining intermediate result and intermediate result using index tag
> Zipcode
> > > The result is no records

> > > If you remove the b-cursor from the from-clause in your select-statement
> > > jou'll get output like this:

> > > >Rushmore optimalisation level for intermediate result: none
> > > The result is three records
> > > >Using index tag Zipcode to rushmore optimize intermediate result
> > > >Rushmore optimalisation level for intermediate result: full
> > > The result is three records
> > > (p.s. i used a SET DELETED OFF before running the program, otherwise the
> > > optimalisation is partial)

> > > Further testing gives the indication that when the use of an index is
> > > supressed in the 2nd select, the results are correct (three records).
> This
> > > can be done by ;
> > > 1) modifying the index-statement into 'index on alltrim(zipcode) tag
> > > zipcode', or
> > > 2)by using FORCE in the select :
> > > > select a.city, a.zipcode ;
> > > > from a, b force ;
> > > > where a.zipcode=alltrim(b.zipcode) ;
> > > > into cursor xx
> > > The last indicates with the SYS(3054,11) statement, that a join using a
> > > Cartesian product is created.

> > > But when you use the two given options the select is not optimized,
> which
> > > was the whole meaning of the index-clause, right??
> > > To optimize the select, remove the 2nd cursor from the source:
> > > > select a.city, a.zipcode ;
> > > > from a ;
> > > > where a.zipcode=alltrim(b.zipcode) ;
> > > > into cursor xx
> > > The query is fully optimized(if set deleted is off or an index is also
> added
> > > on deleted() )

> > > Hope this helps you,

> > > --
> > > Sietse Wijnker
> > > Bizzlink Net Services,
> > > Holland.



> > > > Any help with the following would be much appreciated!

> > > > The following program uses SELECT - SQL to extract records from cursor
> > > > a, based on the contents of a matching field in cursor b (the
> zipcode).

> > > > In VPF 5.0a the code behaves differently depending on whether the
> > > > zipcode field is indexed. If it is NOT indexed, the behavior is as
> > > > expected - all 3 records with zipcode='021' are returned. If the field
> > > > IS indexed, no records are returned.

> > > > Running the exact same prg in FPW 2.6a gives the expected result (3
> > > > records returned), whether or not the field is indexed.

> > > > Is this a bug, or am I doing something wrong? If it is a bug, any
> > > > suggestions for a workaround?

> > > > I'm running NT4.0, if that makes any difference.

> > > > Thanks!

> > > > John Howard

> > > > **********************
> > > > procedure testproc
> > > > close all

> > > > create cursor a (city c(40), zipcode c(5))
> > > > insert into a values ('BOSTON','02115')
> > > > insert into a values ('BOSTON','02118')
> > > > insert into a values ('CAMBRIDGE','02138')

> > > > create cursor b (zipcode c(5))
> > > > insert into b values ('021')

> > > > select a.city, a.zipcode ;
> > > > from a, b ;
> > > > where a.zipcode=alltrim(b.zipcode) ;
> > > > into cursor x
> > > > * cursor x has three records

> > > > select a
> > > > index on zipcode tag zipcode

> > > > select a.city, a.zipcode ;
> > > > from a, b ;
> > > > where a.zipcode=alltrim(b.zipcode) ;
> > > > into cursor xx
> > > > * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)

> > > > return
> > > > **********************



Sat, 02 Feb 2002 03:00:00 GMT  
 SELECT - SQL result set dependant on whether table is indexed
John,

For a zipcode which is less than 5 digits in the b table you would need to
use

WHERE SUBSTR(a.Zipcode, x) IN (SELECT SUBSTR(b.Zipcode, x) FROM b)

You could even have a way to enter the x.

--
Cindy Winegarden
Duke Children's Information Systems

|Cindy,
|
|Thanks for your reply.
|
|I did try your solution:
|
|SELECT a.* ;
|FROM a ;
|WHERE a.Zipcode IN ;
|(SELECT Zipcode FROM b)
|
|and in my hands (VFP 5.0a), when b.zipcode contains a 3-digit zipcode,
|it does not return any records, even when a is not indexed on zipcode.
|It does return the correct record, though, when b.zipcode contains a
|5-digit zipcode.
|
|According to the VFP documentation, SET ANSI determines how the "="
|operator works in SQL statements, not SET EXACT. I did look into that,
|and the setting of exact has no influence, whereas with SET ANSI ON the
|query returns 0 records whether or not there is an index on zipcode in
|a. With SET ANSI OFF, the program behaves as I have been describing
|(result depends on whether there is an index).
|
|So I am still in a bit of a bind, unless there is some other environment
|variable that I am not setting properly.
|
|John Howard
|
|>
|> John,
|>
|> These 3 will all work:
|>
|> *!* (Your choice number 1)
|> select a.city, a.zipcode ;
|>  from a ;
|>  where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
|>  into cursor x
|>
|> *!* (Your choice number 2)
|> select a.city, a.zipcode ;
|>  from a ;
|>  where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
|>  into cursor x
|>
|> but unless you are looking for only a few specific codes, this will be
the
|> best:
|> SELECT a.* ;
|> FROM a ;
|> WHERE a.Zipcode IN ;
|> (SELECT Zipcode FROM b)
|>
|> You should also read about the effect that SET EXACT has on string
|> comparisons and play with it.  If there are 3 chars on the left and 5 on
the
|> right they may or may not be "equal".  This of course applies only to the
|> two choices above.
|>
|> If all of the choices in b have the same number of characters (like
you're
|> looking for all the "021"'s you can use
|>
|> WHERE SUBSTR(a.Zipcode, x) IN (SELECT SUBSTR(b.Zipcode, x) FROM b)
|>
|> One more approach - if the b list is relativelly short, loop through b
|> selecting matching records from a"
|> SELECT a
|> =AFIELDS(temparray)
|> CREATE CURSOR Collection FROM ARRAY Temparray    && Make a cursor to
collect
|> all the ones we picked,  with the same structure as a
|>
|> SELECT b
|> SCAN
|>     MyZip = b.Zipcode
|>     SELECT * FROM A WHERE a.Zipcode = MyZip INTO CURSOR Tempcurs
|>     SELECT Collection
|>     APPEND FROM DBF("Tempcurs")
|> ENDSCAN
|>
|> --
|> Cindy Winegarden
|>
|> Duke Children's Information Systems
|> Duke Univeristy Medical Center

|>


|> > Sietse,
|> >
|> > Thanks for your reply.
|> >
|> > What I am trying to do is a bit more complicated. Generally, the cursor
|> > b will have multiple records. The intent is to extract records from
|> > cursor (or table) a which have zipcodes matching *any* of the zipcodes
|> > in b. To do this requires that b be listed in the from-clause.
|> > Otherwise, records are extracted from a for only that zipcode
referenced
|> > by the record pointer in b.
|> >
|> > It is possible to do the same thing with:
|> >
|> >  select a.city, a.zipcode ;
|> >  from a ;
|> >  where (a.zipcode=<zip1> or a.zipcode=<zip2> or a.zipcode=<zip3>) ;
|> >  into cursor x
|> >
|> > or with:
|> >
|> >  select a.city, a.zipcode ;
|> >  from a ;
|> >  where a.zipcode in (<zip1>, <zip2>, <zip3>) ;
|> >  into cursor x
|> >
|> > but the use of a cursor (b) as a lookup table is much faster and
cleaner
|> > - if it only worked!
|> >
|> > John Howard
|> >
|> >

|> > >
|> > > Reading your code, my gues is that you want to select all the values
in
|> a
|> > > lookup-table that are the same in start as the selected value.
|> > > In that case you don't have to add the tablename of the
|> selection-createria
|> > > in the from-statement.
|> > > That way, your problem is also solved.
|> > >
|> > > There is some explanation possible, and to make it visual, i used the
|> > > command
|> > > SYS(3054,11). This enables display of Rushmore optimization levels
for
|> > > queries. to disable it use SYS(3054,0).
|> > > Place SYS(3054,11) before the 1st selection, and look at the output
on
|> your
|> > > screen. With the b-cursor in your from-clause an indication is given
|> that a
|> > > join is made
|> > > Output is something like:
|> > > >Rushmore optimalisation level for intermediate result: none
|> > > >Rushmore optimalisation level for intermediate result: none
|> > > >Joining intermediate result and intermediate result using temp index
|> > > The result is three records
|> > > >Rushmore optimalisation level for intermediate result: none
|> > > >Rushmore optimalisation level for intermediate result: none
|> > > >Joining intermediate result and intermediate result using index tag
|> Zipcode
|> > > The result is no records
|> > >
|> > > If you remove the b-cursor from the from-clause in your
select-statement
|> > > jou'll get output like this:
|> > >
|> > > >Rushmore optimalisation level for intermediate result: none
|> > > The result is three records
|> > > >Using index tag Zipcode to rushmore optimize intermediate result
|> > > >Rushmore optimalisation level for intermediate result: full
|> > > The result is three records
|> > > (p.s. i used a SET DELETED OFF before running the program, otherwise
the
|> > > optimalisation is partial)
|> > >
|> > > Further testing gives the indication that when the use of an index is
|> > > supressed in the 2nd select, the results are correct (three records).
|> This
|> > > can be done by ;
|> > > 1) modifying the index-statement into 'index on alltrim(zipcode) tag
|> > > zipcode', or
|> > > 2)by using FORCE in the select :
|> > > > select a.city, a.zipcode ;
|> > > > from a, b force ;
|> > > > where a.zipcode=alltrim(b.zipcode) ;
|> > > > into cursor xx
|> > > The last indicates with the SYS(3054,11) statement, that a join using
a
|> > > Cartesian product is created.
|> > >
|> > > But when you use the two given options the select is not optimized,
|> which
|> > > was the whole meaning of the index-clause, right??
|> > > To optimize the select, remove the 2nd cursor from the source:
|> > > > select a.city, a.zipcode ;
|> > > > from a ;
|> > > > where a.zipcode=alltrim(b.zipcode) ;
|> > > > into cursor xx
|> > > The query is fully optimized(if set deleted is off or an index is
also
|> added
|> > > on deleted() )
|> > >
|> > > Hope this helps you,
|> > >
|> > > --
|> > > Sietse Wijnker
|> > > Bizzlink Net Services,
|> > > Holland.
|> > >


|> > > > Any help with the following would be much appreciated!
|> > > >
|> > > > The following program uses SELECT - SQL to extract records from
cursor
|> > > > a, based on the contents of a matching field in cursor b (the
|> zipcode).
|> > > >
|> > > > In VPF 5.0a the code behaves differently depending on whether the
|> > > > zipcode field is indexed. If it is NOT indexed, the behavior is as
|> > > > expected - all 3 records with zipcode='021' are returned. If the
field
|> > > > IS indexed, no records are returned.
|> > > >
|> > > > Running the exact same prg in FPW 2.6a gives the expected result (3
|> > > > records returned), whether or not the field is indexed.
|> > > >
|> > > > Is this a bug, or am I doing something wrong? If it is a bug, any
|> > > > suggestions for a workaround?
|> > > >
|> > > > I'm running NT4.0, if that makes any difference.
|> > > >
|> > > > Thanks!
|> > > >
|> > > > John Howard
|> > > >
|> > > > **********************
|> > > > procedure testproc
|> > > > close all
|> > > >
|> > > > create cursor a (city c(40), zipcode c(5))
|> > > > insert into a values ('BOSTON','02115')
|> > > > insert into a values ('BOSTON','02118')
|> > > > insert into a values ('CAMBRIDGE','02138')
|> > > >
|> > > > create cursor b (zipcode c(5))
|> > > > insert into b values ('021')
|> > > >
|> > > > select a.city, a.zipcode ;
|> > > > from a, b ;
|> > > > where a.zipcode=alltrim(b.zipcode) ;
|> > > > into cursor x
|> > > > * cursor x has three records
|> > > >
|> > > > select a
|> > > > index on zipcode tag zipcode
|> > > >
|> > > > select a.city, a.zipcode ;
|> > > > from a, b ;
|> > > > where a.zipcode=alltrim(b.zipcode) ;
|> > > > into cursor xx
|> > > > * cursor xx has no records (VFP 5.0a) or three records (FPW 2.6a)
|> > > >
|> > > > return
|> > > > **********************
|> >



Sat, 02 Feb 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Select - SQL without a result set

2. Problem of putting the result of SQL Select to an existing table

3. Different results with SELECT SQL and DELETE SQL.

4. what settings can effect results of SELECT SQL

5. SELECT SQL return wrong result

6. SQL - Query - How to Get Result Set

7. FP 2.5 erratic Select SQL results when using date fields

8. Select - SQL gives inconsistent results

9. results: Filtering no-match record/field (SELECT-SQL)

10. IIF results in a Select - SQL statement

11. Slow SQL Select results - help?

12. VP7 - Views - Does it bring in the entire table or only result set

 

 
Powered by phpBB® Forum Software