I do this kind of thing on a daily basis and I write code so I can
understand what I did. I would:
select stocktable
scan
replace qty with getsumqty(stocktable.stockcode)
endscan
procedure getsumqty
lparameters lccode
lnarea = select(0)
do your thing to create the sum you want
select (lnarea)
return (lnreturn)
You can do the same thing with delete
scan
if needsdeleting(stocktable.stockcode)
delete
endif
endscan
a little bit more code, but in a huge monolithic application it is easy
to see what I was doing.
YMMV as Fred would say.
On 7/29/2014 4:15 PM, Sytze de Boer wrote:
I want to the fastest possible qty update to the stocktable with
information from another table
The tables are stand-alone tables
On Wed, Jul 30, 2014 at 11:10 AM, Fernando D. Bozzo <fdbo...@gmail.com>
wrote:
You can use a Select-SQL as a subquery for another Select-SQL, but a
REPLACE or DELETE commands, not being SQL, can't use subqueries, except you
use it inside a function called as part of the REPLACE/DELETE expression
condition (but it can complicate things too much).
Sometimes it's better to have more readability, even if this implies the
use of more than 1 line of code.
What are you exactly want to do?
2014-07-30 1:00 GMT+02:00 Sytze de Boer <sytze.k...@gmail.com>:
I seem to recall a similar line of code where I could something like
sele stocktable
delete for (select sum(qty) as ttl, stockcode from othertable)
I was hoping to do this in 1 line, but as Tracy said, maybe a scan will
have to suffice
scan
select SUM(qty) as ttl,stockcode FROM invtable where
stockcode=stocktable.code
if ttl=0
sele stocktable
delete
endif
endscan
On Wed, Jul 30, 2014 at 10:52 AM, Fernando D. Bozzo <fdbo...@gmail.com>
wrote:
Hi Sytze:
You can't use _tally this way. You are assuming too much using like
this,
and this system variable is updated with every VFP command that
operates
on
records, as Select-SQL, REPLACE, UPDATE, and the like.
In your example, _tally is updated *after* REPLACE is executed with the
number of records affected, but can be affected also with the
Select-SQL
at
the same time.
You didn't mention what the error is, but you can't use a Select-SQL
as a
function or expression, because it is a command. You should decompose
this,
may be using this Select-SQL inside a function, but the _tally
behaviour
can be erratic in this use case.
Best regards.-
2014-07-30 0:22 GMT+02:00 Sytze de Boer <sytze.k...@gmail.com>:
Friends
Can someone point out me error here please
select stocktable
REPLACE ALL qty WITH IIF(_tally>0,select SUM(qty) as ttl,stockcode
FROM
invtable where stockcode=stocktable.code,ttl,0))
--
Kind regards,
Sytze de Boer
Kiss Software
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/53d82fb5.9010...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.