Index Expression to Big ! Help 4

[frankone]

THE EXPRESSION IS
INDEX ON cmpny_code+IIF(next usage<REGIS_DATE.AND.next_mon
<>0,DTOC(next_usage)+STR(next_mon,2,0),"9999999999")+type+
IIF(INT(INT(nbr/100/2)=INT(nbr/100)/2,STR(INT/100),3),STR
(INT(nbr/100)-1,3))+ RIGHT(STR(nbr,5),2)+STR(nbr,5) TO C:\
TEMP\PT_EQREG.TMP

Foxpro lan 2.0

Thank You

 

[JRB-Bldr]

If the Index expression is too big (i.e. too long), then you cannot get around the problem.

Maybe using an Index to achieve your goals is not the best approach. Instead, you might consider approaching the task from another direction.

With more information about what you are trying to accomplish we might be able to advise you better.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[tesar]

frankone,
as 220 B is max lenght,
try split it into database field ik1, ik2, and then
INDEX ON cmpny_code + ik1 + type + ik2 TO C:\
TEMP\PT_EQREG.TMP
where "valid" function by next_usage, next_mon, REGIS_DATE (?) is
REPL ik1 with IIF(next usage<REGIS_DATE.AND.next_mon
<>0,DTOC(next_usage)+STR(next_mon,2,0),"9999999999")
and similarly by nbr
REPL ik2 with IIF(INT(INT(nbr/100/2)=INT(nbr/100)/2,STR(INT/100),3),STR
(INT(nbr/100)-1,3))+ RIGHT(STR(nbr,5),2)+STR(nbr,5)

P.S.
Command word "type" used as variable is not beautiful.
Tesar

 

[DSummZZZ]

You would be better off creating a lot simpler index on the desired fields, then either using SCAN, SEEK or SELECT based on the desired criteria in your code.
Let Fox do the work rather than you.
Something like:

INDEX ON cmpny_code + DTOC(next_usage) + STR(next_mon) + type + nbr ;
   TO C:\TEMP\PT_EQREG.TMP 

SCAN FOR cmpny_code = ? .AND. ;
   (next_usage < REGIS_DATE .AND. next_mon <> 0)...

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[JRB-Bldr]

Dave (above) is echoing my suggestion.

Another approach to the SCAN/ENDSCAN approach might be to use an SQL Query to get the records desired. Again, there is a line length limit of 220 characters, but perhaps you can simplify things and, in conjunction, use a smaller index expression.

We are still looking for you to explain what you are trying to do so that we can better advise you.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[rob444]

If you insist on using such an index expression just create a field long enough to hold that expression and just index on that field.

Rob.

 

[frankone]

Follow up Thank You all for you help
I use Robb444 solution

The follow up question is the orginal expression works on
Novell 5.0, but the error occurs on Window 2000 server
any idea why?

 

[severach]

Your expression is incorrect as posted. It probably went bad when you hacked your sensitive information out of it. I have fixed it and deobfuscated some code:

INDEX ON cmpny_code+;
IIF(next_usage<REGIS_DATE.AND.next_mon<>0,DTOC(next_usage,1)+STR(next_mon,2,0),"9999999999")+;
type+;
STR(2*INT(NBR/200),3)+ ;
RIGHT(STR(nbr,5),2)+STR(nbr,5) ;
TO C:\TEMP\PT_EQREG.TMP

Fixed:
DTOC() and "9999999999" were different sizes depending on your SET DATE. DTOC(...,1) fixes this. I don't know if that usage is valid in Fox 2.0. If not, SET DATE TO YMD and use CHRT(DTOC(...),"/","").

Your even number code had some errors "IIF(INT(INT()=INT(),,) and STR(INT/100)" so I fixed it then realized that what you were doing could be done entirely with math and no IIF. Less code, less bugs.

The way you check any index expression is to BROW FIEL L=<index-subexpression>,m=<index-subexpression> and examine them for validity. Cut out each of your expressions and check the results against mine.
I examined some of yours to figure this out.