Hi foxgang.
I'm trying something I have not been doing for some time. No rude comments
please.
I have two tables. Both of which are used twice. For now just think of one
table used twice. There are two counters in the table and the second points
to the first so for example, record 1 may point to record 150. So I have a
relation set and if I move the pointer in the first table, the relative
record will be shown in the second. ok that's fine. But now I want to select
fields from each table.
Select distinct ;
table1.field1 as f1,;
table1.field2 as f2,;
table2.field3 as f3,;
table2.field4 as f4;
into cursor mycursor.
Despite the relation set I seem to get the records from table1 ok, but the
records from table2 are only the first record. So while f1 and f2 are filled
correctly, f3 and f4 are record 1 all the time. And not the related record.
Can what I want to do be done. Im sure IO did this in VFP3 (shows how long
ago it was) but it fails in VFP9 SP2.
Any clues please
Al
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/724B2A7D11F5497F9370574014D43994@gslredacer
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hi Allen,
if doing a select you need to "relate" the two tables by the pointer fields.
Something like :
Select ;
table1.field1 as f1,;
table1.field2 as f2,;
table2.field3 as f3,;
table2.field4 as f4;
from table1 inner join table2 on table1.pointer = table2.pointer;
into cursor mycursor.
Frank.
Frank Cazabon
On 13/03/2012 01:00 PM, Allen wrote:
> Hi foxgang.
> I'm trying something I have not been doing for some time. No rude comments
> please.
> I have two tables. Both of which are used twice. For now just think of one
> table used twice. There are two counters in the table and the second points
> to the first so for example, record 1 may point to record 150. So I have a
> relation set and if I move the pointer in the first table, the relative
> record will be shown in the second. ok that's fine. But now I want to select
> fields from each table.
> Select distinct ;
> table1.field1 as f1,;
> table1.field2 as f2,;
> table2.field3 as f3,;
> table2.field4 as f4;
> into cursor mycursor.
>
> Despite the relation set I seem to get the records from table1 ok, but the
> records from table2 are only the first record. So while f1 and f2 are filled
> correctly, f3 and f4 are record 1 all the time. And not the related record.
> Can what I want to do be done. Im sure IO did this in VFP3 (shows how long
> ago it was) but it fails in VFP9 SP2.
>
> Any clues please
>
> Al
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F5F801D.2030800@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Tue, Mar 13, 2012 at 12:00 PM, Allen <profox@gatwicksoftware.com> wrote:
> Hi foxgang.
> I'm trying something I have not been doing for some time. No rude comments
> please.
> I have two tables. Both of which are used twice. For now just think of one
> table used twice. There are two counters in the table and the second points
> to the first so for example, record 1 may point to record 150. So I have a
> relation set and if I move the pointer in the first table, the relative
> record will be shown in the second. ok that's fine. But now I want to select
> fields from each table.
> Select distinct ;
> table1.field1 as f1,;
> table1.field2 as f2,;
> table2.field3 as f3,;
> table2.field4 as f4;
> into cursor mycursor.
>
> Despite the relation set I seem to get the records from table1 ok, but the
> records from table2 are only the first record. So while f1 and f2 are filled
> correctly, f3 and f4 are record 1 all the time. And not the related record.
> Can what I want to do be done. Im sure IO did this in VFP3 (shows how long
> ago it was) but it fails in VFP9 SP2.
>
> Any clues please
-------------------------------
Terminate the relationship, do the query and rest the relationship.
--
Stephen Russell
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJTJXdA2xiAOHSVayiNt525qFNaouYCjQeNSNq3BbQ26Q@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Al, You need to have a join in the select statement which reflects the relationship as SQL meremy opens a copy of the table to do the extract so as far as it is concerned the two tables won't be related.
Dave
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf Of Allen
Sent: 13 March 2012 17:00
To: profox@leafe.com
Subject: relations VFP9 SP2
Hi foxgang.
I'm trying something I have not been doing for some time. No rude comments please.
I have two tables. Both of which are used twice. For now just think of one table used twice. There are two counters in the table and the second points to the first so for example, record 1 may point to record 150. So I have a relation set and if I move the pointer in the first table, the relative record will be shown in the second. ok that's fine. But now I want to select fields from each table.
Select distinct ;
table1.field1 as f1,;
table1.field2 as f2,;
table2.field3 as f3,;
table2.field4 as f4;
into cursor mycursor.
Despite the relation set I seem to get the records from table1 ok, but the records from table2 are only the first record. So while f1 and f2 are filled correctly, f3 and f4 are record 1 all the time. And not the related record.
Can what I want to do be done. Im sure IO did this in VFP3 (shows how long ago it was) but it fails in VFP9 SP2.
Any clues please
Al
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BD031ECABF2B60499200AAB3DBB4A999921F7D50@EX-A-FPL.FPL.LOCAL
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Allen <profox@gatwicksoftware.com> wrote:
>Hi foxgang.
>I'm trying something I have not been doing for some time. No rude
>comments
>please.
>I have two tables. Both of which are used twice. For now just think of
>one
>table used twice. There are two counters in the table and the second
>points
>to the first so for example, record 1 may point to record 150. So I
>have a
>relation set and if I move the pointer in the first table, the relative
>
>record will be shown in the second. ok that's fine. But now I want to
>select
>fields from each table.
>Select distinct ;
>table1.field1 as f1,;
>table1.field2 as f2,;
>table2.field3 as f3,;
>table2.field4 as f4;
>into cursor mycursor.
>
>Despite the relation set I seem to get the records from table1 ok, but
>the
>records from table2 are only the first record. So while f1 and f2 are
>filled
>correctly, f3 and f4 are record 1 all the time. And not the related
>record.
>Can what I want to do be done. Im sure IO did this in VFP3 (shows how
>long
>ago it was) but it fails in VFP9 SP2.
>
>Any clues please
>
>Al
>
>
VFP Relations are used for displaying data (report or grid) or scanning the data.
Selections use the join clause and ignore active related tables.
--
Tracy Pearson
PowerChurch Software
Sent with K-9 Mail.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/158e33b5-8178-4ecb-b578-2a87f7aeb835@email.android.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Thanks for all replies. I now have an idea for the morning
Thanks all
Al
-----Original Message-----
From: Frank Cazabon
Sent: Tuesday, March 13, 2012 6:13 PM
To: profoxtech@leafe.com
Subject: Re: relations VFP9 SP2
Hi Allen,
if doing a select you need to "relate" the two tables by the pointer fields.
Something like :
Select ;
table1.field1 as f1,;
table1.field2 as f2,;
table2.field3 as f3,;
table2.field4 as f4;
from table1 inner join table2 on table1.pointer = table2.pointer;
into cursor mycursor.
Frank.
Frank Cazabon
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/26568C03D77A422DB4D0F607D14238D2@gslredacer
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
SQL seems to be integrated into the VFP language, but there are really two
engines in VFP: xBase and SQL. There are many similarities, but also
differences. For instance, xBase uses SET EXACT and SET NEAR while SQL
abides to SET ANSI. Rushmore optimization rules are different between the
two.
Relations are part of the xBase world. They are completely ignored by the
SQL engine (technically, because the tables are opened a second time using
a random alias).
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAL4QJhh154b-TourvpsE-_z8YBJefFfsY-0PWx-qgaOx0u3Tmg@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.