help debug one line of code 1

[friend01]

Hi All,

I'm at a loss of this one. I get an error when I do this command. Any help would be greatly appreciated. Also, at the same time, maybe try to make it more efficient. The command is as follows:

RECALL FOR custtype='RES' AND amount_rem in (sele remark FROM remarks WHERE EMPTY(remarks.invdetgrp))

Please help whenever youcan.


Thanks,
F1

 

[Mike Lewis]

Friend,

You are confusing a native FoxPro command (RECALL) with SQL syntax ( ... IN ...). You can't do that.

Because there is no SQL equivalent of RECALL, you will have to do it in a loop. Something like this:

SELECT Customer && or whatever
SET DELETED OFF
SCAN FOR custtype='RES' 
  SELECT Remarks
  LOCATE FOR Customer.Amount_Rem = Remarks.Remark ;
    AND EMPTY(remarks.invdetgrp)
  IF FOUND()
    RECALL IN Customer
  ENDIF
  SELECT Customer
ENDSCAN

This was written in a hurry and not tested, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[friend01]

but there is a "recall for".... isn't there?

 

[JRB-Bldr]

Yes there is a RECALL FOR... command but it works on VFP expressions, not a SQL statement.

Something like:

RECALL FOR !DELETED()
  or 
RECALL FOR !EMPTY(MyFld1)
  or 
RECALL FOR MyFld1 + MyFld2 >= 145

Good Luck,
JRB-Bldr

 

[friend01]

That's what I thought. Thanks Guys.

 

[Stella740pl]

When I need to do something similar to what you are trying to do, I do it this way:

SELECT remark ;
   FROM remarks ;
   WHERE EMPTY(remarks.invdetgrp)) ;
   INTO ARRAY empty_remarks

RECALL FOR custtype='RES' AND ASCAN(empty_remarks, amount_rem)>0 IN MyTable

Although you should test, Mike's code may turn out faster.

 

[friend01]

Yup, Mike's seems to be nice. Star Mike!

 

[Mike Lewis]

Friend, glad to be of help. Thanks for the star.

Stella: I like your idea of doing FOR .. ASCAN(). Very neat. However, would it be a good idea to wrap it in IF _TALLY > 0 / ENDIF, on the basis that the array won't be created if no records are returned?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Stella740pl]

Mike, yes, I usually do the _TALLY check in real code whenever there is a chance of it returning 0. I've already got burned by a similar thing once or twice. But here I just put a quick snippet not pretending to be a complete solution, just an idea.

Sometimes I use macrosubstitution for that:

SELECT remark ;
   FROM remarks ;
   WHERE EMPTY(remarks.invdetgrp)) ;
   INTO ARRAY empty_remarks

condition2=IIF(_TALLY>0,  ;
               "AND ASCAN(empty_remarks, amount_rem)>0", ;
               <other condition or none at all>)

RECALL FOR custtype='RES' &condition2 IN MyTable

 

[Mike Lewis]

Looks good, Stella. Nice code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Instead of creating an array and using ascan, you should setup a relation and then you can incorporate fields of related tables in a FOR condition or you can test for !EOF('relatedtable') to check the existance of records in the related table.

Bye, Olaf.

 

[Stella740pl]

Olaf, that's a good idea - for "relate-able" tables. Not in all cases they are, and conditions are not always exactly like shown here. But thanks, I didn't think of that (probably because I dislike them way back from my Clipper days, don't know why). I suppose, it would make things faster in those cases where relations are applicable.

 

[DSummZZZ]

I'm with Olaf on this one. Set up a relation between whatever table "custtype" is in, the you can do a recall:

Something like:

SELECT remarks
SET ORDER TO amount_rem
SELECT custtable
SET RELATION TO amount_rem INTO remarks

RECALL FOR custtype='RES' .AND. ;
    !EOF('remarks') .AND. ;
    EMPTY(remarks.invdetgrp)

-Dave Summers-

Even more Fox stuff at:

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