AW: having problem with ROLLUP

Not Sure what syntax you are using, but the following statement works for
me:

Create view qtemp/Myview (Total, Col1, Col2)
as
Select Sum(ColQty * ColPrice), Col1, Col2

From MyTable

Group by RollUp(Col1, Col2);


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Hoteltravelfundotcom
Gesendet: Tuesday, 11.2 2014 17:20
An: Midrange Systems Technical Discussion
Betreff: Re: having problem with ROLLUP

I get an error, Column or global variable TOTAL not found. DO we
need to define TOTAL?


On Tue, Feb 11, 2014 at 10:42 AM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>wrote:

Sure you need a name for you Total field.

Create View ... (Total, IDDCDOCD, IDPRT#) As (SELECT
sum(prt.IDSHP# * IDNTU$), IDDOCD, IDPRT#
FROM DTALIB/OEINDLID prt
WHERE IDDOCD >= 20130101
GROUP by ROLLUP(prt.IDPRT#, prt.IDDOCD)

Or

Create View ...
As (SELECT sum(prt.IDSHP# * IDNTU$) Total, IDDOCD, IDPRT#
FROM DTALIB/OEINDLID prt
WHERE IDDOCD >= 20130101
GROUP by ROLLUP(prt.IDPRT#, prt.IDDOCD)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Tuesday, 11.2 2014 16:25
An: Midrange Systems Technical Discussion
Betreff: Re: having problem with ROLLUP

the only issue now is that this works SELECT
sum(prt.IDSHP# * IDNTU$), IDDOCD, IDPRT#
FROM DTALIB/OEINDLID prt
WHERE IDDOCD >= 20130101
GROUP by ROLLUP(prt.IDPRT#, prt.IDDOCD)

as a select.
but when i tried to make this into a view, it gives an error message:
"Column
list required. "


On Mon, Feb 10, 2014 at 7:12 PM, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

You might look into Grouping Sets.

Instead of rollup, maybe something like GROUP by GROUPING
SETS(prt.IDPRT#, prt.IDDOCD)

This is a good article on grouping an totaling here:

http://www.itjungle.com/fhg/fhg031109-story02.html

Sam


On 2/10/2014 6:48 PM, Hoteltravelfundotcom wrote:

this sql is very good but one thing. this is the result it gives:

.00000000 -
64.95000000 20,130,729 AAB-GLBB11
64.95000000 - AAB-GLBB11
12.95000000 20,130,726 AAB-PLWDIN810
12.95000000 - AAB-PLWDIN810
76.45000000 20,130,529 AAB-TRBAT00
152.90000000 20,130,614 AAB-TRBAT00
229.35000000 - AAB-TRBAT00

so it has a total row for the product. however I dont really need
that total row. in the reporting tool, I am using parameters for
dates so i mainly care about the dates totals. Would there be a
way to surpress that part# total?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.