Change Do While to SQL

[warik]

Dear all,

Can I do it with SQL ?

table1.dbf = (code, desc, qty)
table2.dbf = (code, desc, qty, msg)


sele 1
use table1.dbf

sele 2
use table2.dbf

sele 1
do while !eof()
tcode=code
tdescr=descr
tqty=qty
tmsg="OK"

if tqty<10
tqty=0
tmsg=&quot;WARNING&quot;
endi

sele 2
appe blan
repl code with tcode,;
descr with tdescr,;
qty with tqty,;
msg with tmsg

sele 1
skip

endd


I would like to make it with SQL, hopely it could be faster.

ps. I use VFP 5.0

Thank you in advance

 

[darrellblackhawk]

CLOSE DATA ALL

* Simulate the existence of your table1 and table2

CREATE TABLE (ADDBS(SYS(2023))+&quot;table1&quot (CODE N(4), DESC c(20), qty N(3))

FOR i = 1 TO 1000
* Add records of which every 5th record will get a qty < 10
INSERT INTO table1 (CODE, DESC, qty) VALUES (i, &quot;Desc of item&quot;+TRANSFORM(i), IIF(MOD(i,5)==0,9,100))
NEXT

CREATE TABLE (ADDBS(SYS(2023))+&quot;table2&quot (CODE N(4), DESC c(20), qty N(3), msg c(20))


** The code that does the work

SELECT CODE, DESC, &quot;Warning&quot; AS msg, 0 AS qty FROM table1 WHERE table1.qty < 10 INTO CURSOR failed

SELECT table2

APPEND FROM DBF(ALIAS(&quot;failed&quot)

***

MESSAGEBOX(&quot;Make sure to delete the temp tables in your VFP temp folder&quot;,48,&quot;Success I hope!&quot

GO TOP

BROW NOWAIT

Darrell


'We all must do the hard bits so when we get bit we know where to bite'

 

[TheRambler]

How about:

CREATE TABLE &quot;table2&quot; (CODE N(4), DESC c(20), qty N(3), msg c(20))
append from table1
replace all qty with 0, msg with &quot;Warning&quot; for qty<10

 

[AndrewCoates]

select code, descr, iif(qty < 10, 0, qty) as qty, ;
  padr(iif(qty < 10, 'WARNING', 'OK), 7) as msg ;
  from table1 ;
  into cursor interim

sele 0
use table2
append from (dbf('interim'))
use in interim

If you want to create table2 each time, you can just use:

select code, descr, iif(qty < 10, 0, qty) as qty, ;
  padr(iif(qty < 10, 'WARNING', 'OK), 7) as msg ;
  from table1 ;
  into table table2

Cheers,

Andrew

Andrew Coates

http://www.civilsolutions.com.au/

OzFox 2003 Australia's VFP Conference --

http://www.ozfox.com.au/

------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)

 

[warik]

Yes, you give me alot of option for changing Do While, but I think what Andrew better because it only execute I command for filter to write at new table (table2).

Thank you very much

 

[AndrewCoates]

One small modification -- you may want to specify the length of the qty field like this:

select code, descr, iif(qty < 10, 0000000000, qty) as qty, ;
  padr(iif(qty < 10, 'WARNING', 'OK), 7) as msg ;
  from table1 ;
  into table table2

In case the first row does have a qty < 10

This means you'll end up with a N(10, 0) field for qty. Adjust the 00000000 as appropriate to get the length and precision you need.

Cheers,

Andrew

Andrew Coates

http://www.civilsolutions.com.au/

OzFox 2003 Australia's VFP Conference --

http://www.ozfox.com.au/

------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)

 

[flintstone42]

Suggest you stick with you &quot;Parsing&quot;(looping) solution
instead of an SQL solution.

An SQL solution will be usally be slow
partically on a large database (Over 100,000) records

using a SCAN / ENDSCAN instead of a do while
will work faster in VFP

also a
select sele1
scatter name tempname
select sele2
append blank
gather name tempname

using a scatter namespace seems faster then memory operations.

 

[warik]

Thank's Andres.

Flintstone, Is that right for that situation SQL is slower that SCAN ?
I thought SQL better than SCAN statment...

Thank's

 

[rgbean]

Warik,
It's been my experience that Andrew's SQL solution will work faster than the &quot;old&quot; SCAN technique - especially with more records.

Flintstone42
Have you got any real numbers to back up your claim? What was the environment for your test? How many fields were in the two tables? Were you doing any explict ordering, and were there any indexes in use? Were you using any &quot;filtering&quot; (WHERE clause)?

I'd expect that just VFP's interpretation of 6 statements (or actually more for the two checks) in a loop are going to take more time than the &quot;one-shot&quot; of an SQL statement.

Rick

 

[darrellblackhawk]

Ooops! Didn't add the 'Ok' records...

'We all must do the hard bits so when we get bit we know where to bite'