AW: having problem with ROLLUP

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.