Thanks Dave.
I think you understood the situation correctly. However, I may have
over-simplified the question by omitting that there is also an additional
multi-valued field that I am totaling.
I have used the NUM.VALUES option for other downloads and forgot about it under
this situation. Using NUM.VALUES and TOTAL yields a non-numeric error.
The actual TCL statement is more like
SORT CASH-REC BY WHSE BY CASH-ACCT# BY BATCH# TOTAL CASH MISC.GL TOTAL
DEDUCTIONS BREAK-ON WHSE BREAK-ON CASH-ACCT# BREAK-ON BATCH 08:36:22am 09 Jul
2010 P
AGE 3
CASH-REC..... CASH........ MISC.GL DEDUCTIONS CO. CASH BATCH...
A/C#
MISC 199866 47.99 6134 -33.00 2 1015
6136 -14.99
47.99 -47.99 ***
In this case, CASH is single valued and Deductions are multi-valued, but the
CASH total is not inflated.
As always, thank you for your help
Vance Alspach
John Deere Landscapes
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of David L. Rotman
Sent: Friday, July 09, 2010 8:16 AM
To: [email protected]; [email protected]
Subject: Re: [U2] Cedarville Download (totals using BY.EXP)
Vance--
The behavior you are seeing reflects the same behavior you would get with the
Unidata LIST statement. I assume Universe would produce similar results.
Using BY.EXP causes LIST and DOWNLOAD to produce an output record for each of
the multivalues in your field INVOICE. So, running a total on CASH is going to
give an inflated value.
Here is an approach that may accomplish what you want:
SSELECT CASH-REC BY WHSE BY DATE
DOWNLOAD CASH-REC _
@ID _
INVOICE _
NUM.VALUES ALL MV.ORIENTATION VERTICAL _
CASH _
FOOTING.ON FINAL LITERAL 'Grand' LITERAL 'Total' TOTAL CASH _
HEADING LITERAL 'Record' LITERAL 'Invoice' LITERAL 'Cash'
This should produce output like this (assuming several invoices per record):
"Record","Invoice","Cash"
"308","123456",27.00
,"789012"
,"408765"
"417","333322",55.00
,"333444"
,"412738"
,"510632"
,"230288"
"Grand","Total",82.00
Please let me know if I have misunderstood what you were trying to do.
Dave Rotman
Associate Vice President for Technology
Chief Information Officer
Cedarville University
251 N. Main Street
Cedarville, OH 45314
[email protected]
voice 937-766-7905
fax 937-766-8819
>>> On 7/8/2010 at 2:40 PM, in message
>>> <[email protected]>, "Alspach,
>>> Vance" <[email protected]> wrote:
AIX 5.3
Universe 10.2.4 Pick
We have been using Cedarville DOWNLOAD for simple extracts rather developing
custom programs for each extract. We are now converting some more complex
reports to extracts and having a bit of difficulty.
Has anybody ever tried TOTALing a single valued field while using BY.EXP on
another field with Cedarville Download? It seems to accumulate the total for
each multivalue. This is only an example of what I am experiencing, not the
actual DOWNLOAD command.
SSELECT CASH-REC BY WHSE BY DATE BY-EXP INVOICE
DOWNLOAD CASH-REC BY.EXP INVOICE TOTAL CASH INVOICE
CASH INVOICE
27.00 123456
789654
55.00 333322
333444
--------
164.00
Acting like,
CASH INVOICE
27.00 123456
27.00 789654
55.00 333322
55.00 333444
--------
164.00
TIA,
Vance Alspach
John Deere Landscapes
CONFIDENTIALITY. This electronic mail and any files transmitted with it may
contain information proprietary to Deere & Company, or one of its subsidiaries
or affiliates, and are intended solely for the use of the individual or entity
to whom they are addressed, shall be maintained in confidence and not disclosed
to third parties without the written consent of the sender. If you are not the
intended recipient or the person responsible for delivering the electronic mail
to the intended recipient, be advised that you have received this electronic
mail in error and that any use, dissemination, forwarding, printing, or copying
of this electronic mail is strictly prohibited. If you have received this
electronic mail in error, please immediately notify the sender by return mail.
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
