Total to <Fieldname>... is <FieldExpr> ok?

I'm looking at some old code that uses the total to command. The help file
specifies that the syntax is total to <TableName> on <FieldName> (ie group on
a single field value), but the code I see uses a field expression, ie
<Field1+Field2+Fieldx+etc>. It seems to work ok. Can anyone confirm that this
will always be the case or are there gotcha's.... like what happens when the
FIeldExpression reaches a certain length or the actual field widths exceed a
certain total.
-Lew

Lew
Wed Sep 19 12:46:01 PDT 2007

I'm thinking that, since the command will use an existing index, the value of
field expr must be limited to the standard index key width (currently 100
(idx)/240 (cdx)). On the other hand, total to also works with tables in
groupable physical order without idx/cdx support. If the fox creates a temp
index to accomplish this might there be problems?

Man-wai
Thu Sep 20 05:30:19 PDT 2007

Lew wrote:
> I'm looking at some old code that uses the total to command. The help file
> specifies that the syntax is total to <TableName> on <FieldName> (ie group on
> a single field value), but the code I see uses a field expression, ie
> <Field1+Field2+Fieldx+etc>. It seems to work ok. Can anyone confirm that this

Why not use SQL aggregate??

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 20:28:01 up 9 days 10:47 1 user load average: 0.00 0.00 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Lew
Thu Sep 20 06:12:00 PDT 2007

...because I'm not looking for an alternative. I'm just trying to find out
the limits to the total to ... command. Thanks.

Man-wai
Thu Sep 20 06:24:48 PDT 2007

Lew wrote:
> ...because I'm not looking for an alternative. I'm just trying to find out
> the limits to the total to ... command. Thanks.
>

Getting the work done is more important. Then you could go home earlier
for more sleep. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 21:23:01 up 9 days 11:42 1 user load average: 0.00 0.00 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Lew
Thu Sep 20 06:36:02 PDT 2007

My work *is* to understand the total to command, not replace it. I have
actually been assigned the task of learning what one and can't do with total
to. My work will *not* be done until I have this answer.

Man-wai
Thu Sep 20 06:45:58 PDT 2007

Lew wrote:
> My work *is* to understand the total to command, not replace it. I have
> actually been assigned the task of learning what one and can't do with total
> to. My work will *not* be done until I have this answer.

If you know the intention of that line, then you don't need to know the
old way to do it. If you don't know, then don't touch it.

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 21:44:01 up 9 days 12:03 1 user load average: 0.11 0.20 0.14
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Thu Sep 20 06:48:09 PDT 2007

Man-wai Chang ToDie wrote:
> Lew wrote:
>> My work *is* to understand the total to command, not replace it. I have
>> actually been assigned the task of learning what one and can't do with total
>> to. My work will *not* be done until I have this answer.
>
> If you know the intention of that line, then you don't need to know the
> old way to do it. If you don't know, then don't touch it.
>

Some *EVIL* programmers deliberately make use of un-documented features
or program bugs. So you want to bet your mortgage on it?

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 21:46:01 up 9 days 12:05 1 user load average: 0.23 0.21 0.15
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Thu Sep 20 06:53:01 PDT 2007

>
> Some *EVIL* programmers deliberately make use of un-documented features
> or program bugs. So you want to bet your mortgage on it?

Or you could find a stupid one to do it... hall.. hall... hall...

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 21:51:01 up 9 days 12:10 1 user load average: 0.15 0.21 0.16
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Lew
Thu Sep 20 06:54:17 PDT 2007

Thanks for your advice. It's totally useless and without merit. Perhaps
someone who knows the answer to my question can respond. Evidently, you do
not.
Thanks again.
-Lew

Roger
Thu Sep 20 07:02:44 PDT 2007


"Lew" <Lew@discussions.microsoft.com> wrote in message
news:CF20F317-49EF-4850-A88A-5A9B952EB6A8@microsoft.com...
> My work *is* to understand the total to command, not replace it. I have
> actually been assigned the task of learning what one and can't do with total
> to.

Then do what you've been assigned to do and don't cheat by
asking us here to do your homework for you! <s>

>My work will *not* be done until I have this answer.

A man with a mission. Love it.

-Roger

Man-wai
Thu Sep 20 07:07:51 PDT 2007

Lew wrote:
> Thanks for your advice. It's totally useless and without merit. Perhaps
> someone who knows the answer to my question can respond. Evidently, you do
> not.

Any comments before that line? Did your boss hire a real pro with decent
pay to do the job?


--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 22:06:01 up 9 days 12:25 1 user load average: 0.01 0.02 0.06
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Thu Sep 20 07:12:59 PDT 2007

Lew wrote:
> Thanks for your advice. It's totally useless and without merit. Perhaps
> someone who knows the answer to my question can respond. Evidently, you do
> not.

Maybe Bill Gates could help. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 22:11:01 up 9 days 12:30 1 user load average: 0.00 0.01 0.04
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Mark
Thu Sep 20 11:02:07 PDT 2007

Hi Lew,
From Hacker's Guide to Visual Foxpro

TOTAL TO cTable ON uField
[ FIELDS nNumericFieldList ]
[ Scope ]
[ FOR lForExpression ]
[ WHILE lWhileExpression ]
[ NOOPTIMIZE ]

TOTAL is similar to working with GROUP BY in SELECT-SQL, but far more
limited. It groups records from the table open in the current work area based
on the listed field (uField) and totals the numeric fields listed in
nNumericFieldList. If FIELDS is omitted, all numeric fields are totaled. A
new table is created to hold the result. All fields from the original table
are included in the result. You can limit the records included in the
computation using the Scope, FOR and WHILE clauses.

So what else is wrong with TOTAL? The new table created has the same
structure as the original. If the total for a field is too big to fit, you
lose precision. Also, since all fields are carried along, you get random
results for fields that aren't either totaled or listed in the ON clause.
(Actually, this is the same thing that happens when you include extraneous
fields in a GROUPed query, but at least there you have a chance to omit
them.) TOTAL is limited to working with a single tableâ??you can't match up
records from multiple tables. Finally, you can't compute expressions, only
sum existing fields, so you can't do things like total (quantity*unit price)
to get an invoice total.

hth

Mark


"Lew" wrote:

> I'm looking at some old code that uses the total to command. The help file
> specifies that the syntax is total to <TableName> on <FieldName> (ie group on
> a single field value), but the code I see uses a field expression, ie
> <Field1+Field2+Fieldx+etc>. It seems to work ok. Can anyone confirm that this
> will always be the case or are there gotcha's.... like what happens when the
> FIeldExpression reaches a certain length or the actual field widths exceed a
> certain total.
> -Lew

Gene
Thu Sep 20 11:23:43 PDT 2007

Lew <Lew@discussions.microsoft.com> wrote:

>Thanks for your advice. It's totally useless and without merit. Perhaps
>someone who knows the answer to my question can respond. Evidently, you do
>not.

Fair wanring: Perhaps, someone who knows the answer can decide
that he would not want to be treated in the same way and so will
ignore your posts.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Lew
Thu Sep 20 11:48:01 PDT 2007

Thanks, Mark. I'm already aware of the syntax and limitations described in
HG. My question is about the uField in the syntax statement you posted. It
seems that uField may actually be a concatination of many fields as well --
which is a departure from the VFP help doc as well. This works in the small
data samples I've tested and brings me to 2 other questions. First, recall
that total will use an existing index to establish groups or use physical row
order if no index is available. This suggests that there may be limits
imposed by index limits ... I believe an index expression is limited to 128
chars (in a cdx) and must exaluate to no more than a 240 byte value. This
suggests, but does not prove, that the concatinated uFieldList must observe
similar limitations. My next question is whether or not these hypothetical
index - type restrictions apply when the physical record is used unsupported
by an index.

Lew
Thu Sep 20 12:28:03 PDT 2007

Oops... here's a mistake in HG. They state that "The new table created has
the same structure as the original. If the total for a field is too big to
fit, you
lose precision." This is *not* the case. I created a test table that
contained a column defined as n(4,2) and then appended 100 recs using 99.5 as
the appended value for that column. When I ran the total to command, VFP
created a column defined as n(7,2) which was sufficient to hold the results.
-Lew

Man-wai
Thu Sep 20 19:19:00 PDT 2007

> lose precision." This is *not* the case. I created a test table that
> contained a column defined as n(4,2) and then appended 100 recs using 99.5 as
> the appended value for that column. When I ran the total to command, VFP
> created a column defined as n(7,2) which was sufficient to hold the results.

SELECT SQL is the best solution. Find out the objective of that program
line and do it with SQL.

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 10:16:01 up 10 days 35 min 1 user load average: 0.08 0.04 0.01
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Lew
Fri Sep 21 06:44:06 PDT 2007

...and if you were assigned to document the code without changing it, would
you still go ahead and change it anyway or would you refuse to do your job?

Tony
Fri Sep 21 07:06:32 PDT 2007

"Lew" <Lew@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:B0F8DAD7-07CA-4495-B557-114454541AEE@microsoft.com...
> Thanks, Mark. I'm already aware of the syntax and limitations described in
> HG. My question is about the uField in the syntax statement you posted. It
> seems that uField may actually be a concatination of many fields as well --
> which is a departure from the VFP help doc as well. This works in the small
> data samples I've tested and brings me to 2 other questions. First, recall
> that total will use an existing index to establish groups or use physical row
> order if no index is available. This suggests that there may be limits
> imposed by index limits ... I believe an index expression is limited to 128
> chars (in a cdx) and must exaluate to no more than a 240 byte value. This
> suggests, but does not prove, that the concatinated uFieldList must observe
> similar limitations. My next question is whether or not these hypothetical
> index - type restrictions apply when the physical record is used unsupported
> by an index.

Hi, Lew,

This is not documented in VFP. However, I have used these feature for more
than 15 years without mistakes. I would say the uField mentioned above can be
character fields concatenated together for making total. You are partly right
on the length of index expression. To my knowledge, the expression limit of an
index with idx extension is is 100 and of cdx extension is 240 bytes. Similarly,
the limit of uField in the TOTAL command is 240 bytes. (I am still using
VFP 6.0. I don't know the limits for newer versions). The reason for using index
is for faster summing up the numeric fields being totalled and to arrange your output
table in a known sequence. It seems this is not a must. Without the index, the
output table depends on the arrangement of your data that may be in random order.

HTH

- Tony

Tony
Fri Sep 21 07:20:49 PDT 2007

"Lew" <Lew@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:93B7639F-619A-4687-8D14-BB7759C56099@microsoft.com...
> Oops... here's a mistake in HG. They state that "The new table created has
> the same structure as the original. If the total for a field is too big to
> fit, you
> lose precision." This is *not* the case. I created a test table that
> contained a column defined as n(4,2) and then appended 100 recs using 99.5 as
> the appended value for that column. When I ran the total to command, VFP
> created a column defined as n(7,2) which was sufficient to hold the results.
> -Lew

Hi, Lew,

The limit of VFP is not that little. Try to create a test table with numeric fields,
say, n(18,2) or n(20,0) and append adequete amount of records with large
numeric values to it and do the test on TOTAL again.

- Tony

Tony
Fri Sep 21 07:36:05 PDT 2007


"Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:eXSMbW$%23HHA.1416@TK2MSFTNGP03.phx.gbl...
>> lose precision." This is *not* the case. I created a test table that contained a column defined as n(4,2) and then appended 100
>> recs using 99.5 as the appended value for that column. When I ran the total to command, VFP created a column defined as n(7,2)
>> which was sufficient to hold the results.
>
> SELECT SQL is the best solution. Find out the objective of that program line and do it with SQL.

Hi, Toylet,

Although I have to write programs with SQL statement daily, I think it is not
bad to know more. Up to now, I still believe the SQL could not replace
all the features of VFP.

Btw, could you remember the guy who help testing the speed of
your linux FTP in those netforum days?

- Tony

Lew
Fri Sep 21 07:56:04 PDT 2007

Finally! Thank you, Tony!

Tony
Fri Sep 21 08:17:16 PDT 2007


"Tony" <tony@somewhere.com> ¼¶¼g©ó¶l¥ó·s»D:%23CWdAzF$HHA.4732@TK2MSFTNGP04.phx.gbl...
>
> "Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:eXSMbW$%23HHA.1416@TK2MSFTNGP03.phx.gbl...
>>> lose precision." This is *not* the case. I created a test table that contained a column defined as n(4,2) and then appended 100
>>> recs using 99.5 as the appended value for that column. When I ran the total to command, VFP created a column defined as n(7,2)
>>> which was sufficient to hold the results.
>>
>> SELECT SQL is the best solution. Find out the objective of that program line and do it with SQL.
>
> Hi, Toylet,
>
> Although I have to write programs with SQL statement daily, I think it is not
> bad to know more. Up to now, I still believe the SQL could not replace
> all the features of VFP.
>
> Btw, could you remember the guy who help testing the speed of
> your linux FTP in those netforum days?
>
> - Tony

my bad memory
the guy helped testing your FTP should be in those hkpcug days

- Tony

Man-wai
Fri Sep 21 21:11:30 PDT 2007

>> Btw, could you remember the guy who help testing the speed of
>> your linux FTP in those netforum days?
> my bad memory
> the guy helped testing your FTP should be in those hkpcug days

Too long time ago... can't remember now. I also set up a torrent server
in home linux server. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 12:09:01 up 20:25 1 user load average: 0.16 0.19 0.10
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Fri Sep 21 21:12:58 PDT 2007

> Although I have to write programs with SQL statement daily, I think it is not
> bad to know more. Up to now, I still believe the SQL could not replace
> all the features of VFP.

Most... not all. But theoretically, you should be able to use SQL
INSERT, UPDATE, DELETE to replace all traditional APPEND, REPLACE,
DELETE, SUM, COPY TO. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 12:10:01 up 20:26 1 user load average: 0.06 0.15 0.09
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Tony
Sat Sep 22 01:08:05 PDT 2007

"Lew" <Lew@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:0E086E3F-638B-4894-B3B1-9465BC48F79A@microsoft.com...
> Finally! Thank you, Tony!

Use the TOTAL with care. I say indexing is not a must because the
VFP allow you to execute TOTAL command without checking whether
the table is indexed or not. If your table is in the sequence of uField,
you really don't need an index. Otherwise, you may get undesirable result.
What TOTAL does is:
It scans through the table being totalled and if the uField of current record
does not equal to the uField of the last record in the output table or the ouput
table is blank, it will append the current record of the table being totalled to the
output table. Otherwise it will add the values of numeric fields of current record
to the corresponding numeric fields of last record in the output table.
If your table being totalled is in random order, your output table not be
summed up according to uField as expected.

- Tony

Tony
Sat Sep 22 01:48:04 PDT 2007

"Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:O%23Nu65M$HHA.1900@TK2MSFTNGP02.phx.gbl...
>>> Btw, could you remember the guy who help testing the speed of
>>> your linux FTP in those netforum days?
>> my bad memory
>> the guy helped testing your FTP should be in those hkpcug days
>
> Too long time ago... can't remember now. I also set up a torrent server in home linux server. :)

The internet traffic of your home PC will be too busy for such kind of server.
Need your PC stay on line by 7*24?

Man-wai
Sat Sep 22 02:47:32 PDT 2007

> The internet traffic of your home PC will be too busy for such kind of server.
> Need your PC stay on line by 7*24?

My home Linux is a 7*24 server.

But once I set up a torrent server, it used up all connections when
robots found my private BT server and announced it. So I removed it now.

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 17:44:01 up 1 day 2:00 1 user load average: 0.07 0.02 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Sat Sep 22 07:47:32 PDT 2007

> My home Linux is a 7*24 server.
> But once I set up a torrent server, it used up all connections when
> robots found my private BT server and announced it. So I removed it now.

Anyway, my ftp server is still running. Anonymous users could upload
only. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 22:45:01 up 1 day 7:01 1 user load average: 0.07 0.06 0.01
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Tony
Sat Sep 22 08:08:44 PDT 2007

I am anonymous user now.

"Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:eBfDVdS$HHA.5164@TK2MSFTNGP05.phx.gbl...
>> My home Linux is a 7*24 server.
>> But once I set up a torrent server, it used up all connections when robots found my private BT server and announced it. So I
>> removed it now.
>
> Anyway, my ftp server is still running. Anonymous users could upload only. :)
>
> --
> @~@ Might, Courage, Vision, SINCERITY.
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
> ^ ^ 22:45:01 up 1 day 7:01 1 user load average: 0.07 0.06 0.01
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Tony
Sat Sep 22 08:12:18 PDT 2007


"Tony" <tony@somewhere.com> ¼¶¼g©ó¶l¥ó·s»D:eWVW6pS$HHA.3940@TK2MSFTNGP05.phx.gbl...
>I am anonymous user now.
>
> "Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:eBfDVdS$HHA.5164@TK2MSFTNGP05.phx.gbl...
>>> My home Linux is a 7*24 server.
>>> But once I set up a torrent server, it used up all connections when robots found my private BT server and announced it. So I
>>> removed it now.
>>
>> Anyway, my ftp server is still running. Anonymous users could upload only. :)
>>

Anything I can upload?

- Anonymous

Man-wai
Sun Sep 23 06:19:01 PDT 2007

>>> Anyway, my ftp server is still running. Anonymous users could upload only. :)
> Anything I can upload?

Whatever you liked.. I have rTorrent running all the time as a GNU
Screen session, usually downloading anime-related stuffs. :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.7
^ ^ 21:16:01 up 31 min 0 users load average: 0.04 0.01 0.03
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Tony
Sun Sep 23 08:23:23 PDT 2007

"Man-wai Chang ToDie" <toylet.toylet@gmail.com> ???????:OakNgQe$HHA.4956@TK2MSFTNGP06.phx.gbl...
>>>> Anyway, my ftp server is still running. Anonymous users could upload only. :)
>> Anything I can upload?
>
> Whatever you liked.. I have rTorrent running all the time as a GNU Screen session, usually downloading anime-related stuffs. :)
>
> --
> @~@ Might, Courage, Vision, SINCERITY.
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Xubuntu 7.04) Linux 2.6.22.7
> ^ ^ 21:16:01 up 31 min 0 users load average: 0.04 0.01 0.03
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Unfortunately, I don't have much on anime-related stuff.

- Anonymous