FoxPro 9.0 Joining tables with aggregation

Question

I want to aggregate up my final table and don't know how to solve the joining part. I have a Do While loop that goes through my table contracts.dbf that passes each row into a procedure. This procedure test33 uses this information and picks out desired data from other tables. My desired aggregation is all the results from each loop, so if there are 101 rows in contracts.dbf, then joining.dbf will be 101 columns wide.

DELETE FILES *.tmp RECYCLE
SELECT distinct depot_nr FROM bs_case;
INTO table contracts.tmp

SELECT RECNO() as rownum,;
    depot_nr as depot_nr;
FROM contracts.tmp
NbContracts =RECCOUNT()
COPY TO test3.dbf
CLOSE TABLES

counter = 1

DO WHILE counter < NbContracts
    SELECT depot_nr as depot_nr;
    WHERE rownum = counter FROM test3
    test33(depot_nr, counter)
    counter = counter + 1
ENDDO
CLOSE TABLES 

PROCEDURE test33(depot_nr_in, NbofTimes)
use bs_case alias bs
SELECT  Depot_nr                as depot_nr,;
        Psres3pcgb              as psres3pcgb;
    WHERE Depot_nr = depot_nr_in FROM bs INTO TABLE toJoin.tmp

DO CASE 
    CASE NbofTimes = 1
        SELECT * FROM toJoin.tmp 
        COPY TO joining.dbf
    CASE NbofTimes = NbContracts
        ?counter
        SELECT * FROM bsP.tmp as one LEFT JOIN joining.dbf as aggregated; && ERROR HERE
        ON (one.depot_nr = aggregated.depot_nr) into table joining.dbf
        CLOSE TABLES
        ENDPROC
    Otherwise 
        SELECT * FROM toJoin.tmp as one LEFT JOIN joining.dbf as aggregated;   && ERROR HERE
        ON (one.depot_nr = aggregated.depot_nr) into table joining.dbf
        CLOSE TABLES
ENDCASE
CLOSE TABLES
CLOSE DATABASES
ENDPROC

The data looks like

bs_case
===================================
depot_nr      Psres3pcgb
22              123
31              222
22              345
32              444
23              222
22              222

contracts.dbf
===================================
22
31
32
23

My procedure takes each value in contracts.dbf as a parameter. This is done with do while loop.

I want the final result to be a table from each run of test33 procedure. E.g.

Loop 1
===============
   22
the result

Loop2
==============
    22          31
test33(22)   test33(31)

Loop3
==============
    22          31             32
test33(22)   test33(31)     test33(32)

Loop4
==============
    22          31             32              23
test33(22)   test33(31)     test33(32)     test33(23)

Each result from test33(##) is a column of values. I hope this gives a better picture of what I am tying to do

What you are trying to do in your procedure is very non-productive. It looks like you want some sort of cross-tabulation, but very inefficient. Can you please provide some sample of the data... Say 3 or 4 "Depot_NR" entries worth of the original data. Then, show what you EXPECT the results to be presented. That would give a much better chance of helping you out. — DRapp

Tamar E. Granor Tamar E. Granor · Accepted Answer · 2012-12-14T21:38:19

Unless you need to store the results for a future run, you never need to SELECT INTO TABLE or COPY TO.

It seems likely to me that you don't need the subroutine or the loop at all. Can you please show some sample data and the desired result. Use CREATE CURSOR and INSERT INTO to show the sample data, so anyone who wants to help can just copy those lines and create the test data.

FoxPro 9.0 Joining tables with aggregation

1 Answers