REPLACE - Mailing list pgsql-sql

From lawyerkill@aol.com (LawyerKill)
Subject REPLACE
Date
Msg-id 20040131165006.19898.00001138@mb-m17.aol.com
Whole thread Raw
List pgsql-sql
LK

I'm using Foxpro 8 and I'm trying to merge selective data from 4 different
tables into a new table.The problem is that the tables are not alike, Table one
may have 4 records with the name John in them, table 2 had only one John with
one account # in that record, so I want to have a new table with the 4
Johns(Same Johns) and the account number each time after his name. So here is
what I did. I selected data from each table and stored them in an array, 3 of
the four table have a common field which is, 'Name' 

Create table data
Name char (12),;
Address char (20),;
Phone Char (10),;
Account Char (20),;
Order N(15),;
Date Date,;
(etc.) 


Select Name, Address,  Phone (etc.) From Table 1;
where Table1.date= todays date;
INTO ARRAY ABC
(Then I count the number of records)
Num1=Alen(ABC,1)

Select Name, AccountN, Order, time (etc) from Table2;
Where Table2.date= todays date;   
INTO ARRAY DFE
(Then I count the number of records)
Num2=Alen(DFE,1)

Now since Table 1 has the most data I move that data into the new table.

Use Data
Append blank
For XXX=1 to Num1(Record count from array)
Replace Name with ABE(xxx,1)
Replace Address with ABE(xxx,2)
Replace Phone with ABE(xxx,3)
(Etc)
Skip
Endfor|Next

So far so good, now here is where the problem happens. Let's say Johnname
appears 4 times in table 1, and had one account Num in table 2, lets say it's
ASD234 and I have 36 records all together from Table 1. Now I try and replace
the account number, there are only 9 different records in table 2, 9 account
numers to match to the 36 records from Table 1.

For SA = 1 to Num2(Second array count total of 9 records)
Replace Account with DEF[SA,1] Where  ABC[1,1]=DEF[SA,1]
ENDFOR|Next

Now what happens is that it replaces all 36 records with the same account
number, ASD234, it seems to ignore the, ABC[1,1]=DEF SA[SA,1]. What I was
looking for was this

John ASD234
John ASD234  
John ASD234
John ASD234  
Joe (no number)
Joe (no number)
Mike (no number)
Etc.

What I get is this,
John ASD234
John ASD234  
John ASD234
John ASD234  
Joe ASD234
Joe ASD234
Mike ASD234
Etc.  

I also tried to use the For command, for ABC[1,1]=DEF SA[SA,1].

I tried using the Locate command and the Insert command, but same
thing, it doesn't stop replacing account wqith the same account number, all 36
records.  

For some reason it's not comparing the two expressions like it should and
stopping when they no longer equal each other. Maybe you can't compare 2 arrays
like I'm trying to do. I would be most thankful if anyone can solve this
problem.

Any idea why it's ignore the equal sign and just replacing them all?


pgsql-sql by date:

Previous
From: Tomasz Myrta
Date:
Subject: Re: An order by question
Next
From: JinNet Picker
Date:
Subject: Postgres "dblink" Help