how to use "if exists (select)"

[AidanPryde]

My database is made up of free files, no sql backend.

Can I add a line like this to my utility?:

if exists (select name from table where name = "jim")

or

if "jim" .not. in (select name from table where name = 'jim')

or
cnum = 4
if "jim" in (select name from table where number = (cNum))
else
endif

 

[AidanPryde]

I have item numbers which should be unique, and I want to renumber them.

However, I want to make sure that when I renumber them, the number is not already in use.

It doesn't really matter what they are as long as they are not being used. But I'd rather them be sequential.

This is what I have so far:

DO while .not. EOF()
if cust_num > str(newmax,6)
newmax = (newmax + 1)
if newmax .not. in (select cust_num from cust where cust_num = currcust)
currcust = STR(((newmax) + 10000),6)
select cust_num from cust where cust_num = currcust
select cinv
scan for cust_num = custmr->cust_num
repl cust_num with currcust
endscan
select cashy
scan for cust_num = custmr->cust_num
repl cust_num with currcust
endscan
select custmr
repl cust_num with currcust
endif
ENDIF
SKIP
ENDDO

 

[steve4king]

I haven't seen _tally before..
But what I've done in the past for this kind of thing is:

 select whatever from whatever nowait
if reccount() = 0
...
..

 

[AidanPryde]

That's going to be really slow though. It's going to have to sort through probably 50,000 records several hundred times that way..

I suppose I could select just that value to a seperate table then query that instead.

Any better ideas/methods?
Maybe make a primary key on it then skip if theres an error?

I dont know how to do that in VFP though.

 

[mmerlinn]

[ ]

Seems to me that the best way is to place existing numbers into an ordered temporary TABLE, CURSOR, or ARRAY.

Then loop through your customer base checking the new numbers against the existing numbers in that temporary TABLE, etc.

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[JRB-Bldr]

Why not just have an index on the field of interest, then you need only do a SEEK on the desired value.

If the SEEK fails, then the value does not exist and vice versa.

* --- Sometime earlier ---
USE Table IN 0 EXCL
SELECT Table 
INDEX ON Name TAG Name

* --- Then when needed ---
USE Table in 0
SELECT Table
SET ORDER Name
IF SEEK('jim')
   * --- Found ---
   < Do Whatever >
ELSE
   * --- Not Found ---
   < Do Other Whatever >
ENDIF

Good Luck,
JRB-Bldr

 

[dgrewe]

If you have a index on CUSTMR.CUST_NUM, try this

STORE SELECT() to lnWorkArea
SELECT 0
USE custmr EXCLU
ALTER Table custmr ADD COLUMN newnumber (I)
REPLACE ALL newnumber WITH RECNO()
SET INDEX TO Cust_Num

SELECT 0
USE CINV
SET RELATION TO CUST_NUM INTO custmr
REPLACE ALL cinv.cust_num WITH custmr.newnumber FOR !EOF("custmr")
USE 

SELECT 0
USE cashy
SET RELATION TO cust_num INTO custmr
REPLACE ALL cashy.cust_num WITH custmr.newnumber FOR !EOF("custmr")
USE

SELECT CUSTMR
SET ORDER TO 
REPLACE ALL cust_num WITH newnumber
ALTER table drop COLUMN newnumber
USE

Select (lnWorkArea)

Needless to say this is untest code and you should backup your data first before running the code.

David W. Grewe Dave

 

[craigber]

SELECT MAX(Cust_Num) INTO CURSOR cMaxNum

SELECT CustMr
REPLACE ALL Cust_Num WITH cMaxNum.Cust_Num + RECNO()

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[AidanPryde]

Lot of good ideas on here.
I went with JRB-Bldr's suggestion first, but came across something odd.

When it gets to the last line in the file it tells me that the alias does not exist (even though it has been using that alias several cycles already)

SET STEP ON
close data all
set talk on
SET DEFAULT TO d:\business
CLOSE DATABASES
use customer in 0 alias custcnt
USE customer again IN 0 ALIAS custmr
SET ORDER TO custnum
use invnumbers in 0 alias iNo
use receipt_line in 0 alias cinv
use receipt in 0 alias cashy
currcust = 0

newmax = INPUTBOX("Enter the maximum valid customer number","Max valid custnum","  ",0,"--","--")
IF newmax != "--"
	newmax = VAL(newmax)
	IF newmax > 0
		IF newmax < 990000
			select custmr
			counter = newmax
			DO while .not. EOF()
				if cust_num > str(newmax,6)
					Counter = (counter + 1)
					currcust = STR(((counter) + 10000),6)
					SELECT custcnt
					IF .not. seek (currcust)
						select cinv
						scan for cust_num = custmr->cust_num
							repl cust_num with currcust
						endscan
						select cashy
						scan for cust_num = custmr->cust_num
							repl cust_num with currcust
						endscan
						select custmr
						repl cust_num with currcust
						SKIP
					ENDIF
				ELSE
				skip
				ENDIF
			ENDDO eof
			select ino
			goto bott
			REPLACE custno WITH ((counter) + 10000)
		Else
			Messagebox("Value entered is too high")
		ENDIF
	Else
		Messagebox("Value is invalid")
	ENDIF

Else
	MESSAGEBOX("Operation cancelled",32)
ENDIF
CLOSE DATABASES all

 

[AidanPryde]

Oops, I can't edit this script.. but I didn't have the problem listed above after that one time. It worked fine, a little slow. I replaced all of the 'scan for's with 'replace all's.

Lots of great ideas on here though.
Mike, thanks for that last info, unfortunately I'm not actually a programmer for this so I cannot manipulate the databases really, just the data.

It worked fine though.
Thanks everyone for the ideas.

 

[TamarGranor]

If the records already exist and you're doing this as a one-shot deal, not on an ongoing basis, can't you just use:

REPLACE ALL ItemNum WITH RECNO()

Tamar