You may try to change your query as follows:
select case when(Grouping(pdhccd) =1) then 'company totals'
when (Grouping(pdhgln) =1) then 'Gl Totals'
when (Grouping(pdhded) =1) then 'ded Totals'
Else '' End 1,
PDHCCD, PDHGLN, PDHDED,
sum(pdheed) as emptot
from prdhs join PMST on Pdhemp = pmemp
join Prflexgl00 on Pdhgln = glno
where pdhedt between 20140501 and 20140531
and pdheed <> 0
group by Rollup(pdhccd, pdhgln, pdhded)
Order By pdhccd, pdhgln, pdhded
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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Smith, Mike
Gesendet: Friday, 18.7 2014 21:14
An: midrange-l@xxxxxxxxxxxx
Betreff: single sql to contain details and totals.
I'm trying to convert a QM400 query to a rpg program.
Its fairly simple and I'd be done already if I just coded my breaks in the
program, but I'm trying to expand my sql knowledge.
I found an example from an article and can actually get it to work, but I
cannot get my own to work.
My query joins 2 files and simply sums up a deduction amount when there is a
break(the break is at company,gl,ded code) I want to print the details
followed by the totals for the deduction amount.
3300 110,023,220,130,100 41.67 FLXD
3300 110,023,220,130,100 41.67 FLXD
TOTAL 83.34
3300 110,023,220,140,100 40.00 FLXM
3300 110,023,220,140,100 50.00 FLXM
TOTAL 90.00
The query I got to work is from the following
http://odetocode.com/articles/85.aspx
here is my query that I can't get to work
select
case
when(Grouping(pdhccd)=1) then 'company totals'
else pdhccd
end as pdhccd,
case
when (Grouping(pdhgln)=1) then 'Gl Totals'
else pdhgln
end as pdhgln,
case
when (Grouping(pdhded)=1) then 'ded Totals'
else pdhded
end as pdhded,
sum(pdheed) as emptot
from prdhs join PMST on Pdhemp = pmemp where pdhedt between 20140501 and
20140531 and pdhgln in(select glno from prflexgl00) and pdheed <> 0 group by
pdhccd,pdhgln,pdhded with rollup
I only get the summary records and not the details.
I probably only need the GL total or ded total case statement, because they
change at the same time, and I've tried that, but I get basically the same
result.
Any idea what I've got wrong.
If there is an easier way, please let me know
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
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.
midrange.com
