SQL-Select Record no of table1 (table 2 for selection) 
Author Message
 SQL-Select Record no of table1 (table 2 for selection)

I'm giving you a proposal on how to fix your problem here but I must say I
don't think I would be depending upon RECNO() for a row identifier,
especially in a SELECT-SQL statement.  I'd rather define a unique ID column
or hmm.. I guess there's many ways you COULD do it but I'm a believer in
unique ID columns.  Although I don't know what your data looks like, I'd be
concerned about the possibility that RECNO() for a specific row in a or b
could change after a PACK.

Try closing a and b (the tables) right before executing that query and see
what happens and YES you should specify what table in RECNO() - RECNO("a")
or RECNO("b") depending upon which one you are trying to get.

I guess I don't have enough information about your project, but if you're
running into this kind of problem you are possibly basing your solution on
something you shouldn't.  It feels more like a workaround than a solution.

Geoff


Quote:
> What I am trying to do is create a list of ids for a pick list (subset of
all
> ids). User picks the user and that is used to retrieve data from a
releated
> table. Then I want to display decodes of fields in original table of all
ids.

> What I tried to do was save the physical record number of table of ids
which
> has the values to be decoded by doing a sql select from two tables, where
> table b is used to restrict the records goton from table a i.e.

>    select fields_in_a , recno() from a,b
>      where a.field = b.field and b.amt > 0
>      into dbf c

> my problem is that recno() returns record number of record in table b not
> record nunber of table a, recno('a') returns same value for all records,
last
> record number in b.

> There must be a simple way to do this. Any help appreciated.



Fri, 12 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
What I am trying to do is create a list of ids for a pick list (subset of all
ids). User picks the user and that is used to retrieve data from a releated
table. Then I want to display decodes of fields in original table of all ids.

What I tried to do was save the physical record number of table of ids which
has the values to be decoded by doing a sql select from two tables, where
table b is used to restrict the records goton from table a i.e.

   select fields_in_a , recno() from a,b
     where a.field = b.field and b.amt > 0
     into dbf c

my problem is that recno() returns record number of record in table b not
record nunber of table a, recno('a') returns same value for all records, last
record number in b.

There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
Using RECNO() in a SQL SELECT creates unpredicatable results. This is
documented in VFP help.

--

Craig Berntson
Microsoft FoxPro MVP
Salt Lake City Fox User Group
http://members.home.com/foxpro


Quote:
> What I am trying to do is create a list of ids for a pick list (subset of
all
> ids). User picks the user and that is used to retrieve data from a
releated
> table. Then I want to display decodes of fields in original table of all
ids.

> What I tried to do was save the physical record number of table of ids
which
> has the values to be decoded by doing a sql select from two tables, where
> table b is used to restrict the records goton from table a i.e.

>    select fields_in_a , recno() from a,b
>      where a.field = b.field and b.amt > 0
>      into dbf c

> my problem is that recno() returns record number of record in table b not
> record nunber of table a, recno('a') returns same value for all records,
last
> record number in b.

> There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
Try to change tables sequence:

from b,a
where b.field=a.field

But remember, sometimes it is very dangerous.
Results may be unpredictable very often, if
you are using RECNO(), SEEK() and other xBase functions
in sql statements with more than one table.
It will be better for you (and your users) to identify (or add)
unique key.

g

Quote:
>    select fields_in_a , recno() from a,b
>      where a.field = b.field and b.amt > 0
>      into dbf c

> my problem is that recno() returns record number of record in table b not
> record nunber of table a, recno('a') returns same value for all records,
last
> record number in b.

> There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)

says...

Quote:

>Using RECNO() in a SQL SELECT creates unpredicatable results. This is
>documented in VFP help.

VFP 5, I must have missed it.  Where is that documented?   Thanks
Quote:
>--

>Craig Berntson
>Microsoft FoxPro MVP
>Salt Lake City Fox User Group
>http://members.home.com/foxpro



>> What I am trying to do is create a list of ids for a pick list (subset of
>all
>> ids). User picks the user and that is used to retrieve data from a
>releated
>> table. Then I want to display decodes of fields in original table of all
>ids.

>> What I tried to do was save the physical record number of table of ids
>which
>> has the values to be decoded by doing a sql select from two tables, where
>> table b is used to restrict the records goton from table a i.e.

>>    select fields_in_a , recno() from a,b
>>      where a.field = b.field and b.amt > 0
>>      into dbf c

>> my problem is that recno() returns record number of record in table b not
>> record nunber of table a, recno('a') returns same value for all records,
>last
>> record number in b.

>> There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)

says...
Thanks for your response.
Tables do have unique keys.  What I am try to do is the following:

1.  Get a list of clients who have have non-zero balances in their accounts.
    Client data is in one table, balance information is in second table.
2.  Display information about the client.  Some of the information are codes
    which need to be translated into words (values kept in other tables).

This produces a pick list for the user who then choses a client for whom data
is to be displayed.

My first try was to get get a list of keys and the record number and then go
to that record.  Obviouslly that is not working.  My alternatives seem to be
to save decode data via the SELECT-SQL statement or use SEEK to find the
desired record.  I am leaning towards the SEEK but if that is the more
efficent way of doing what I need.  I am open to suggestions.

Quote:

>I'm giving you a proposal on how to fix your problem here but I must say I
>don't think I would be depending upon RECNO() for a row identifier,
>especially in a SELECT-SQL statement.  I'd rather define a unique ID column
>or hmm.. I guess there's many ways you COULD do it but I'm a believer in
>unique ID columns.  Although I don't know what your data looks like, I'd be
>concerned about the possibility that RECNO() for a specific row in a or b
>could change after a PACK.

>Try closing a and b (the tables) right before executing that query and see
>what happens and YES you should specify what table in RECNO() - RECNO("a")
>or RECNO("b") depending upon which one you are trying to get.

>I guess I don't have enough information about your project, but if you're
>running into this kind of problem you are possibly basing your solution on
>something you shouldn't.  It feels more like a workaround than a solution.

>Geoff



>> What I am trying to do is create a list of ids for a pick list (subset of
>all
>> ids). User picks the user and that is used to retrieve data from a
>releated
>> table. Then I want to display decodes of fields in original table of all
>ids.

>> What I tried to do was save the physical record number of table of ids
>which
>> has the values to be decoded by doing a sql select from two tables, where
>> table b is used to restrict the records goton from table a i.e.

>>    select fields_in_a , recno() from a,b
>>      where a.field = b.field and b.amt > 0
>>      into dbf c

>> my problem is that recno() returns record number of record in table b not
>> record nunber of table a, recno('a') returns same value for all records,
>last
>> record number in b.

>> There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
VFP 5.0 Help says:

User-Defined Functions with SELECT   Although using user-defined functions
in the SELECT clause has obvious benefits, you should also consider the
following restrictions:

The speed of operations performed with SELECT may be limited by the speed
at which such user-defined functions are executed. High-volume manipulations
involving user-defined functions may be better accomplished by using API and
user-defined functions written in C or assembly language.
You can assume nothing about the Visual FoxPro input/output (I/O) or table
environment in user-defined functions invoked from SELECT. In general, you
don't know which work area is selected, the name of the current table, or
even the names of the fields being processed. The value of these variables
depends on where precisely in the optimization process the user-defined
function is invoked.

It isn't safe to change the Visual FoxPro I/O or table environment in
user-defined functions invoked from SELECT. In general, the results are
unpredictable.
The only reliable way to pass values to user-defined functions invoked
from SELECT is by the argument list passed to the function when it is
invoked.
If you experiment and discover a supposedly forbidden manipulation that
works correctly in a certain version of FoxPro, there is no guarantee it
will continue to work in later versions.

Quote:

> >Using RECNO() in a SQL SELECT creates unpredicatable results. This is
> >documented in VFP help.

> VFP 5, I must have missed it.  Where is that documented?   Thanks

> >--

> >Craig Berntson
> >Microsoft FoxPro MVP
> >Salt Lake City Fox User Group
> >http://members.home.com/foxpro



> >> What I am trying to do is create a list of ids for a pick list (subset
of
> >all
> >> ids). User picks the user and that is used to retrieve data from a
> >releated
> >> table. Then I want to display decodes of fields in original table of
all
> >ids.

> >> What I tried to do was save the physical record number of table of ids
> >which
> >> has the values to be decoded by doing a sql select from two tables,
where
> >> table b is used to restrict the records goton from table a i.e.

> >>    select fields_in_a , recno() from a,b
> >>      where a.field = b.field and b.amt > 0
> >>      into dbf c

> >> my problem is that recno() returns record number of record in table b
not
> >> record nunber of table a, recno('a') returns same value for all
records,
> >last
> >> record number in b.

> >> There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
Yes.  And if you are buffering your tables, mixing SQL
and Xbase stuff adds even more complications.  SQL SELECT
will always select directly from the table(s) on disk,
however, Xbase commands (like LOCATE, REPLACE, SEEK, etc.)
will operate on the buffered table.  If you mingle them
together thinking you are working on the same data, you
can easily be left scratching your head. :^)

-- TRW

Quote:

> Using RECNO() in a SQL SELECT creates unpredicatable results. This is
> documented in VFP help.

> --

> Craig Berntson
> Microsoft FoxPro MVP
> Salt Lake City Fox User Group
> http://members.home.com/foxpro



> > What I am trying to do is create a list of ids for a pick list (subset of
> all
> > ids). User picks the user and that is used to retrieve data from a
> releated
> > table. Then I want to display decodes of fields in original table of all
> ids.

> > What I tried to do was save the physical record number of table of ids
> which
> > has the values to be decoded by doing a sql select from two tables, where
> > table b is used to restrict the records goton from table a i.e.

> >    select fields_in_a , recno() from a,b
> >      where a.field = b.field and b.amt > 0
> >      into dbf c

> > my problem is that recno() returns record number of record in table b not
> > record nunber of table a, recno('a') returns same value for all records,
> last
> > record number in b.

> > There must be a simple way to do this. Any help appreciated.



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
Recno() cannot be used in SQL select as already mentioned. The proper
way is to use unique primary keys - that's what they are for.

I don't suggest SEEK. This command needs quite careful setup - you have
to open the table, set the index order and know exactly by which
expression
the index is generated. LOCATE is better in that if the index expression
doesn't match the search expression or index happens to be deleted, the
program still works (slower). Also one dont't have to set the index
order. Fox help even says LOCATE is fastest if there is no index order
set.

IMHO the best choise are SQL selects. By using that you don't have to
open tables beforehand. So use smth. like this:

select a.keyfield, a.* from a, b where ... into cursor picklist

-- let user choose a record, then:

key = picklist.keyfield
select * from a,b,c ... where a.keyfield = m.key and ... into cursor
customer

-- display customer info

In case you want also to make updates a parametrized SQL view is the
proper solution.

Rgds,
Paavo.

Quote:
> >> What I tried to do was save the physical record number of table of ids
> >which
> >> has the values to be decoded by doing a sql select from two tables, where
> >> table b is used to restrict the records goton from table a i.e.

> >>    select fields_in_a , recno() from a,b
> >>      where a.field = b.field and b.amt > 0
> >>      into dbf c



Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)
You _can_ use RECNO(), but it is not a very good idea since as Craig said,
it produces unpredictable results.  What I gave to him worked (at least for
me) but I don't recommend it.

You don't need to set the index anymore before using a SEEK since you can
specify the index (I think this is the 3rd parameter) in the SEEK call
itself.  At least this is true in 5.0 forward, I don't remember if it was in
3.0.  As far as LOCATE is concerned, you must select the work area to use it
while you don't have to for SEEK (I think this is the 2nd parameter).

LOCATE must be fastest when no index order is set since you can't use SEEK
without having indexes present to use.  However, LOCATE does present you
with a little flexibility in that it will function without indexes or with
them.  This is good if you anticipate any changes in your index definitions.
Typically, I don't think most classic xBase programs do anticipate these
changes (at least no changed indexes or dropped indexes) since a lot of the
code (for example SEEK) might depend upon them.

I definitely feel SELECT-SQL is the best choice though, for a few reasons.
First, yes it performs excellently.  Second, it is a pretty universal
technique used in database access - you'll find it in VB/Access, SQL-Server,
Oracle.. just about everything.  Thus it's pretty easy for people to follow
and maintain.

Certainly the original poster should find a better solution than using
RECNO() in his SELECT-SQL unless he is coding quick-and-dirty things that
are not mission-critical.

Geoff


Quote:
> Recno() cannot be used in SQL select as already mentioned. The proper
> way is to use unique primary keys - that's what they are for.

> I don't suggest SEEK. This command needs quite careful setup - you have
> to open the table, set the index order and know exactly by which
> expression
> the index is generated. LOCATE is better in that if the index expression
> doesn't match the search expression or index happens to be deleted, the
> program still works (slower). Also one dont't have to set the index
> order. Fox help even says LOCATE is fastest if there is no index order
> set.

> IMHO the best choise are SQL selects. By using that you don't have to
> open tables beforehand. So use smth. like this:

> select a.keyfield, a.* from a, b where ... into cursor picklist

> -- let user choose a record, then:

> key = picklist.keyfield
> select * from a,b,c ... where a.keyfield = m.key and ... into cursor
> customer

> -- display customer info

> In case you want also to make updates a parametrized SQL view is the
> proper solution.

[signature and earlier posts snipped]


Sat, 13 Apr 2002 03:00:00 GMT  
 SQL-Select Record no of table1 (table 2 for selection)


Thanks to every one who responded.  

Quote:
>Yes.  And if you are buffering your tables, mixing SQL
>and Xbase stuff adds even more complications.  SQL SELECT
>will always select directly from the table(s) on disk,
>however, Xbase commands (like LOCATE, REPLACE, SEEK, etc.)
>will operate on the buffered table.  If you mingle them
>together thinking you are working on the same data, you
>can easily be left scratching your head. :^)

>-- TRW


>> Using RECNO() in a SQL SELECT creates unpredicatable results. This is
>> documented in VFP help.

>> --

>> Craig Berntson
>> Microsoft FoxPro MVP
>> Salt Lake City Fox User Group
>> http://members.home.com/foxpro



>> > What I am trying to do is create a list of ids for a pick list (subset
of
>> all
>> > ids). User picks the user and that is used to retrieve data from a
>> releated
>> > table. Then I want to display decodes of fields in original table of all
>> ids.

>> > What I tried to do was save the physical record number of table of ids
>> which
>> > has the values to be decoded by doing a sql select from two tables,
where
>> > table b is used to restrict the records goton from table a i.e.

>> >    select fields_in_a , recno() from a,b
>> >      where a.field = b.field and b.amt > 0
>> >      into dbf c

>> > my problem is that recno() returns record number of record in table b
not
>> > record nunber of table a, recno('a') returns same value for all records,
>> last
>> > record number in b.

>> > There must be a simple way to do this. Any help appreciated.



Sun, 14 Apr 2002 03:00:00 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. How can I limit the number of records selected with SELECT - SQL in FPW

2. SQL, selection records where 2 agregated fields are not equal

3. Wish List: Select * from table1 into Recordset …

4. SQL SELECT not returning all records

5. Update selected records in a 3rd table from a Grid, VFP 5

6. Using SQL SELECT to count certain records

7. FPW 2.6, Selecting records based on criteria in multiple tables

8. SELECT finding other records in same table with date +- 5 days

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

10. VFP6 Select-sql and deleted records

11. Exclude deleted record with SELECT - SQL...

12. Selecting two records from the same child table?

 

 
Powered by phpBB® Forum Software