RE: RPGLE Example for reading SQL GROUP BY ROLLUP result

The aggregate function GROUPING(Column) will return 1 if there is a NULL
value in the specified column otherwise 0 is returned.
The result from the grouping aggregate function can be checked within your
RPG program.

Just check the result of the columns after, try something like

select SalesYear, CustNo,
Sum(Amount) Total,
Grouping(SalesYear) as GrpYear,
Grouping(CustNo) as GrpCustNo
From Sales
Group By RollUp(SalesYear, CustNo)
Order By SalesYear, CustN

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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of gio.cot
Sent: Freitag, 14. April 2017 13:06
To: rpg400-l@xxxxxxxxxxxx
Subject: RPGLE Example for reading SQL GROUP BY ROLLUP result

Hi all



I'm looking for some RPGLE example for reading the following SQL result



SELECT CLCLI, CLUB1, CLUB2, CLUB3, CLCAR, cllsi,

sum(clqis) FROM OCLLw00F WHERE CLSTS <>

'C' GROUP BY ROLLUP


(CLCLI, CLUB1, CLUB2, CLUB3, CLCAR, cllsi )


ORDER BY CLCLI, CLUB1, CLUB2, CLUB3, CLCAR



I would need to check the break level when CLCLI and CLCAR change , how can
I do it ? I check the null value .. how can I check the null value




34815 A01 002 0080502 1952
1,0000

34815 A01 002 0080502 -
1,0000

34815 A01 002 - -
1,0000

34815 A01 002 - - -
1,0000

34815 A01 003 0080501 1951
1,0000

34815 A01 003 0080501 1952
1,0000

34815 A01 003 0080501 -
2,0000

34815 A01 003 0080505 1951
1,0000

34815 A01 003 0080505 -
1,0000

34815 A01 003 - -
3,0000

34815 A01 003 - - -
3,0000

34815 A01 - - - -
4,0000

34815 - - - - -
4,0000

- - - - - -
4,0000

- - - - - -
4,0000





--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD