Scan Problem for Multiple Instances 1

[randallJ]

I have two tables a and b. A contains numbers 1-10, b contains numbers: 1,1,2,3,3,4,5,6,7,8,9,10.

I want to compare the records in a against the records in b. If they match then *do something.
so far I have this however it will not append if there are multiple instances of the same number in b.

SELECT a
GO TOP
DO WHILE NOT EOF()
nrecno=a.numa
SELECT b
SCAN for b.numb=nrecno
m.numb=nrecno
SELECT b
GO bottom
APPEND BLANK
GATHER MEMVAR
endscan
SELECT a
SKIP
ENDDO

The numbers appended at the end are 1,2,3,4,5,6,7,8,9,10

Any suggestions

 

[jimstarr]

After GO BOTTOM and APPEND BLANK you're at the last record in file b, so the SCAN thinks it's done.

Jim

 

[Imaginecorp]

The following is to give you an idea on how to do it.
First you need an index on a field which is common to both tables.

Use a in 0 index pfield
Use b in 0 index pfield
Select a
Scan
Cfield = a.pfield
*** you could do a scatter here
Select b
If Indexseek(cfield)
Indexseek(cfield,.t.)
Scan while cfield = b.pfield
Insert into b (f1,f2….) values (a.f1,af2….)
*** if scatter insert from memvar here
Select b
endscan
Endif
Select a
Endscan

 

[randallJ]

Jimstarr
Thank you for responding.

I have removed GO BOTTOM and achieve the same results, the whole purpose of the program is for it to append blank so I cannot remove this, is there a way around this?

 

[Imaginecorp]

>the whole purpose of the program is for it to append blank >so I cannot remove this
If I understand this correctly:
Replace the Insert statement with Append Blank. This will insert blank records in B.

 

[jimstarr]

After GATHER MEMVAR insert GO TOP.

Jim

 

[randallJ]

Sorry Imaginecorp

I had not seen your response yet, I will run through your code now.

Thanks

 

[jimstarr]

Actually my GO TOP suggestion won't work - it'll cause a loop.

Jim

 

[JRB-Bldr]

There seems to have been a run on questions about comparing two data tables to find duplicates or orphans (the opposite of duplicates)

You might want to look over the following previous postings to see what was recommended.
thread1252-1322963
thread184-1320953
Once you create your code to find records which meet your criteria (i.e. duplicate or orphan) you can do whatever you need to do.

Good Luck,
JRB-Bldr

 

[jimstarr]

Try the following as your SCAN loop:

SCAN FOR b.numb=nrecno
m.numb=nrecno
holdrec = RECNO()
SELECT b
APPEND BLANK
GATHER MEMVAR
GOTO holdrec
ENDSCAN

Jim

 

[randallJ]

Thanks jrbbldr I will look into these.

Jimstarr
Yes this works, I did think of trying this, but dismissed it! Thanks a lot.

 

[brigmar]

Another way to do this without worrying about bouncing your record pointer around all the time is to open a second copy of table b (call it 'c', for example) using the AGAIN option, and just issue your APPENDS to c.