case...endcase or something better

[friend01]

Hi,

I have a brain freeze currently and at a lose on hos to do this. I would think "CASE...ENDCASE" is the best way but I can't for the life of me write the darn code for it. It's pretty simple. Here's my code:

USE MST0910
SCAN FOR amount_rem="3004" AND EMPTY(state)
STORE SUBSTR(tel,1,7) TO t7
STore SUBSTR(tel,1,3) TO t3
SELECT pic_city
SEEK t7
if it's found, sele MST0910 and REPLACE mst0910.state WITH pic_city.prov and move to the next recno() in mst0910. if it's not found, look in a dbf called cell_tel, if it's found in there, sele MST0910 and REPLACE mst0910.state WITH cell_tel.prov and move to the next recno() in mst0910. If it's not found, , using t3 now (not t7) look in a dbf called us_desti, if it's found in there, sele MST0910 and REPLACE mst0910.state WITH us_desti.state. OTHERWISE, just sele MST0910, SKIP.
ENDSCAN

My brain is not working today on how to code that. it must be the snow storm we just got.

Can anybody help me? Please let me know.

Thanks,
F1



ENDSCAN

 

[Mike Lewis]

This sounds more like a nested IF/ELSE/ENDIF than a CASE/ENDCASE. It's not as if you have a whole series of conditions, and you only want to act on the first of them that is true.

I'd go for something like this:

SCAN
  look for something
  IF FOUND()
    do something
  ELSE
    look for something else
    IF FOUND()
      do another thing
    ELSE
      do yet again something else
    ENDIF
  ENDIF
ENDSCAN

I would've thought that was more appropriate thn a CASE/ENDCASE

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[friend01]

Hi Mike,

OK, this is what I have:

It seems a bit slow. Anything you can do to speed up this?

CLOSE DATABASES ALL
CLEAR ALL
CLEA
SELECT 0
USE C:\Users\johnk\Desktop\srtest\mst0910
SELECT 0
USE F:\DATA\pic_city ORDER npanxx
SELECT 0
USE F:\DATA\cell_tel &&very small dbf
INDEX ON npanxx TO idxcelltel
SELECT 0
USE F:\DATA\us_desti ORDER area

SELECT mst0910
REPLACE frstate WITH state, state WITH "" FOR amount_rem='3004' AND EMPTY(Frstate)

SELECT mst0910

SCAN FOR amount_rem="3004" AND EMPTY(state)

T7=SUBSTR(tel,1,7)
T3=SUBSTR(tel,1,3)

SELECT pic_city
SEEK T7
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH pic_city.prov
ELSE
SELECT cell_tel
SEEK T7
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH cell_tel.prov
ELSE
SELECT us_desti
SEEK T3
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH us_desti.state
ELSE
SELECT mst0910
SKIP
ENDIF
ENDIF
ENDIF

ENDSCAN


Please let me know.

Thanks,
F1

 

[friend01]

actually,

I think there's something wrong with my last "ELSE". The one that says "SELECT mst0910. SKIP" basically what I want to do is if it's not found in pic_city or cell_tel or us_desti, sele the mst0910 and go to the next record.

I'm I correct in my code in the last ELSE?

pleaese let me know

Thanks,
F1

 

[friend01]

here's my revised code:

CLOSE DATABASES ALL
CLEAR ALL
CLEA
SELECT 0
USE C:\Users\johnk\Desktop\srtest\mst0910
SELECT 0
USE F:\DATA\pic_city ORDER npanxx
SELECT 0
USE F:\DATA\cell_tel &&very small dbf
INDEX ON npanxx TO idxcelltel
SELECT 0
USE F:\DATA\us_desti ORDER area

SELECT mst0910
REPLACE frstate WITH state, state WITH "" FOR amount_rem='3004' AND EMPTY(Frstate)

SELECT mst0910

SCAN FOR amount_rem="3004" AND EMPTY(state)

T7=SUBSTR(tel,1,7)
T3=SUBSTR(tel,1,3)

SELECT pic_city
SEEK T7
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH pic_city.prov
ELSE
SELECT cell_tel
SEEK T7
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH cell_tel.prov
ELSE
SELECT us_desti
SEEK T3
IF FOUND()
SELECT mst0910
REPLACE mst0910.state WITH us_desti.state
ELSE
SELECT mst0910
REPLACE mst0910.state WITH mst0910.state
ENDIF
ENDIF
ENDIF

ENDSCAN


any opinion on speeding this process?

thanks,
f1

 

[Mike Lewis]

It's impossible to know why your code is running slowly without knowing more about the data (and particularly the presence or otherwise of indexes) and the environment that it's running in.

That said, at first glance, I'd say the FOR clause in the SCAN is a possible culprit. If there are no indexes on Amount_rem or State, and if the table is large, the FOR clause could slow things down.

But really, that's just a guess. In general, you can't know why code is slow just by looking at it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[craigber]

I've simplified the code quite a bit.

The code makes two passes through the table. This code does one pass and has the same funcationality. Also, don't SKIP inside a SCAN/ENDSCAN. It can have undesired side effects.

CLOSE DATABASES ALL
CLEAR ALL
CLEAR

USE C:\Users\johnk\Desktop\srtest\mst0910 IN 0

USE F:\DATA\pic_city IN 0
SET ORDER TO npanxx

USE F:\DATA\cell_tel IN 0
INDEX ON npanxx TO idxcelltel

USE F:\DATA\us_desti IN 0
SET ORDER TO area

SELECT mst0910
SCAN FOR amount_rem="3004" AND (EMPTY(State) OR EMPTY(FrState))
IF EMPTY(FrState)
REPLACE FrState WITH State, State WITH ""
ENDIF

T7=SUBSTR(tel,1,7)
T3=SUBSTR(tel,1,3)

IF SEEK(T7, Pic_City)
REPLACE state WITH pic_city.prov IN Mst0910
ELSE
IF SEEK(T7, Cell_Tel)
REPLACE state WITH cell_tel.prov IN Mst0910
ELSE
IF SEEK(T3, Us_Desti)
REPLACE state WITH us_desti.state IN Mst0910
ENDIF
ENDIF
ENDIF
ENDSCAN


Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[friend01]

Hi Craig,

Are you sure that code does the exact same thing as mine above? I'm not sure your SCAN does as mine does. I do a REPL first then based on that I do the SCAN.

Please let me know.


Thanks,
F1

 

[friend01]

I don't seem to have an index set on my MST.

What would be the most optimized INDEX for it.

I now have:
INDEX ON amount_rem='3004' AND EMPTY(state) TO idxmstr

what do you think. FYI this DBF has over 4 million records in it.

Thanks,
F1

 

[Mike Lewis]

Well, as I said earlier, you need an index on Amount_rem and another on State. With 4 million records, those indexes could make a difference.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[friend01]

Hey Craig,

I just tries some of your code and I get :

VARIABLE PIC_CITY NOT FOUND

at the IF SEEK(T7, Pic_City) command.

Why would that be?

 

[friend01]

Hi Mike,

Is this not good?

INDEX ON amount_rem='3004' AND EMPTY(state) TO idxmstr

let me know,
f1

 

[friend01]

Craig,

I found the error in your code. It should be:

IF SEEK(T7, "Pic_City")


BUT It still takes quite a while. any idea on making this thing fly?

 

[bborissov]

VARIABLE PIC_CITY NOT FOUND

at the IF SEEK(T7, Pic_City) command.

Why would that be?

The second paramater is a character expression, so this could be:

IF SEEK(T7, "Pic_City")

That:

INDEX ON amount_rem='3004' AND EMPTY(state) TO idxmstr

is NOT good, also try to avoid IDX, better index that file once to a CDX with this command:

INDEX ON amount_rem TAG AmountRem

Then:

SEEK PADL("3004", Length of the amount_rem field here)
SCAN WHILE ALLTRIM(amount_rem) == "3004"
     .....
ENDSCAN

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[friend01]

I get a "FILE MUST BE OPENED EXCLUSIVELY" when I try to :

INDEX ON amount_rem TAG AmountRem

I canot open it excl. other people are in it.

any suggestions?

 

[bborissov]

You can't create a structural index unless the table is opened exclusively.
but that is ONE time job.
When you create it it stays and it updates itlesf accordingly.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[friend01]

Oh well, I guess I'll stick with my code and what I have. Thanks.

 

[TamarGranor]

I suspect you could do what you want with a couple of SQL UPDATEs in sequence, something like this:

USE C:\Users\johnk\Desktop\srtest\mst0910 IN 0
REPLACE frstate WITH state, state WITH "" FOR amount_rem='3004' AND EMPTY(Frstate) IN mst0910

UPDATE mst0910 ;
  SET state = pic_city.Prov ;
  FROM pic_city ;
  WHERE amount_rem = "3004" and EMPTY(state) ;
    AND SUBSTR(tel, 1, 7) = pic_city.npanxx && or whatever the field is for that tag

UPDATE mst0910 ;
  SET state = cell_tel.Prov ;
  FROM cell_tel ;
  WHERE amount_rem = "3004" and EMPTY(state) ;
    AND SUBSTR(tel, 1, 7) = cell_tel.npanxx && 

UPDATE mst0910 ;
  SET state = us_desti.Prov ;
  FROM us_desti ;
  WHERE amount_rem = "3004" and EMPTY(state) ;
    AND SUBSTR(tel, 1, 3) = us_desti.area && or whatever the field is for that tag

You'd have to test to see whether this is faster or not, but if you have the appropriate index tags for each of the tables, it should be pretty fast.

Tamar

 

[Mike Lewis]

Friend,

Oh well, I guess I'll stick with my code and what I have.

But you asked why it was slow, and we told you it was probably because of the lack of indexes. Don't give up just because of a couple of mistakes when creating the indexes.

It's really not that difficult. Instead of this:

INDEX ON amount_rem='3004' AND EMPTY(state) TO idxmstr

You need this:

INDEX ON amount_rem TAG Amount
INDEX ON State TAG State

Second, as you've discovered, you need to open the table exclusively before you can index it. Remember, you only have to index the table once. After that, VFP will remember the indexes and use them where appropriate. So you don't need exclusive use for normal day-to-day work.

Give it a try. I can't guarantee that it will solve your problem, but the chances are good that it will.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[friend01]

Thanks Mike and everybody.