Working with multiple tables

[John Cosmas]

I have a situation where I'm trying to fetch data from multiple tables. I
get the records I want from the first, scan through each record, and then
fetch the child records from another table based on the values from the
first table. This obviously uses a nested statement. Please review the
enclosed and let me know if I'm heading down the right path.

SELECT 0
USE "C:\Program Files\GIM Editor\wrk_policy.dbf" SHARED AGAIN

SELECT 1
USE "E:\GIMS\Database\car_stat.dbf" SHARED AGAIN

SELECT 2
USE "C:\Program Files\GIM Editor\wrk_car_stat.dbf" SHARED AGAIN

SCAN
SCATTER MEMVAR
? wrk_policy.k4
? wrk_policy.a
SELECT * FROM car_stat WHERE ;
car_stat.k4 = wrk_policy.k4 AND car_stat.a = wrk_policy.a
SCATTER MEMVAR
INSERT INTO wrk_car_stat FROM MEMVAR

ENDSCAN
USE

This code does not currently run. I can see the records from the first
table (wrk_policy), but I haven't seen it run the SELECT and populate the
second table. Any help in this matter would be most appreciated.

John

[Fred Taylor]

1: Do not specify the work area with your SELECT 1,2 etc. Use the SELECT
0, or just USE yourtable IN 0.

2: Before your SCAN, depending on how you USEd your table, do a SELECT
wrk_car_stat. You really should reference your work areas by the alias name
of the table open in that area.

3: Instead of SCATTER MEMVAR, I'd suggest using SCATTER NAME oRec, but the
first one you have isn't even needed.

4: Instead of doing the SQL SELECT without secifying an INTO location
should be avoided, unless you really want to browse your table.

5: If what you're trying to do is COPY all records that match your WHERE
clause (there may be more than one?) then you should do:

SELECT * FROM car_stat
WHERE car_stat.k4 = wrk_policy.k4 AND car_stat.a = wrk_policy.a ;
INTO CURSOR matches NOFILTER
SELECT wrk_car_stat
APPEND FROM DBF("matches")

As a matter of fact, if I interpret your code correctly, I don't think you
need a SCAN at all, let the SQL SELECT get the records you want.

So, altogether, I think your code would look more like this:

USE "C:\Program Files\GIM Editor\wrk_policy.dbf" SHARED AGAIN IN 0
USE "E:\GIMS\Database\car_stat.dbf" SHARED AGAIN IN 0
USE "C:\Program Files\GIM Editor\wrk_car_stat.dbf" SHARED AGAIN IN 0

SELECT * FROM car_stat ;
WHERE car_stat.k4 = wrk_policy.k4 AND car_stat.a = wrk_policy.a ;
INTO CURSOR matches NOFILTER

SELECT wrk_car_stat
APPEND FROM DBF("matches")

USE IN SELECT("wrk_policy")
USE IN SELECT("car_stat")
USE IN SELECT("wrk_car_stat")
USE IN SELECT("matches")
--
Fred
Microsoft Visual FoxPro MVP

Post by John Cosmas
I have a situation where I'm trying to fetch data from multiple tables. I
get the records I want from the first, scan through each record, and then
fetch the child records from another table based on the values from the
first table. This obviously uses a nested statement. Please review the
enclosed and let me know if I'm heading down the right path.
SELECT 0
USE "C:\Program Files\GIM Editor\wrk_policy.dbf" SHARED AGAIN
SELECT 1
USE "E:\GIMS\Database\car_stat.dbf" SHARED AGAIN
SELECT 2
USE "C:\Program Files\GIM Editor\wrk_car_stat.dbf" SHARED AGAIN
SCAN
SCATTER MEMVAR
? wrk_policy.k4
? wrk_policy.a
SELECT * FROM car_stat WHERE ;
car_stat.k4 = wrk_policy.k4 AND car_stat.a = wrk_policy.a
SCATTER MEMVAR
INSERT INTO wrk_car_stat FROM MEMVAR
ENDSCAN
USE
This code does not currently run. I can see the records from the first
table (wrk_policy), but I haven't seen it run the SELECT and populate the
second table. Any help in this matter would be most appreciated.
John

[Stasys]

Post by Fred Taylor
USE IN SELECT("matches")

Why not

USE IN matches

[Stefan Wuebbe]

Post by Stasys

Post by Fred Taylor
USE IN SELECT("matches")

Why not
USE IN matches

Because Use In Select(cAlias) is safer, i.e. cannot fail when the alias
is not available. Say because it was not open by accident or closed by
another procedure previously, like an error handler etc.


hth
-Stefan