RE: Coding SQL in RPG

Assumed you always have the same select columns but want to show different totals, could handle this with CUBE in the GROUP BY clause, and the GROUPING function in the HAVING clause.

The GROUPING aggregate function can only be used in composition with the multi-dimensional grouping (i.e. ROLLUP, CUBE, GROUPING SETS). It will return 1 if the specified column includes a NULL value and 0 if there is no NULL value.

Example: (SALESV01 is a view that already accumulates my sales on SALESYEAR, CUSTNO and ITEMNO)
Depending on what you set in the HAVING clause (0 or 1) totals are displayed or not. ... and you could handle this with STATIC SQL and HostVariables.
The first columns in only a summary text (depending on the GROUPING Function) which describes the total. It is optional, just to show which Total is displayed.

Select Case When Grouping(SalesYear) = 1
and Grouping(CustNo) = 1
and Grouping(ItemNo) = 1
Then 'Grand Total'
When Grouping(CustNo) = 1
and Grouping(ItemNo) = 1
Then 'Total Year ' concat SalesYear
When Grouping(SalesYear) = 1
and Grouping(CustNo) = 1
Then 'Total ItemNo ' concat ItemNo
When Grouping(SalesYear) = 1
and Grouping(ItemNo) = 1
Then 'Total Customer ' concat CustNo
When Grouping(ItemNo) = 1
Then 'Total Year ' concat SalesYear concat ' / Customer ' concat CustNo
When Grouping(CustNo) = 1
Then 'Total Year ' concat SalesYear concat ' / ItemNo ' concat ItemNo
When Grouping(SalesYear) = 1
Then 'Total Customer ' concat Trim(CustNo) concat ' / ItemNo ' concat ItemNo
Else '' End SummaryText,
SalesYear, CustNo, ItemNo, Sum(Total) as Total
from SalesV01
Group By Cube(SalesYear, CustNo, ItemNo)
Having Grouping(CustNo) >= 0
and Grouping(SalesYear) >= 0
and Grouping(ItemNo) >= 0
Order By SalesYear, CustNo, ItemNo;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

IBM Champion since 2020

"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)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Saturday, 2 September 2023 06:04
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Coding SQL in RPG

A view is always a good idea for complex queries.

But you can also use CASE expressions to solve the problem.

a) it should be possible the use (multiple) CASE expressions directly in the GROUP BY clause - by using

group by case :mySelectorVar
when '1' then a
when '2' then b
when '3' then a
else c
end,
case :mySelectorVar
when '3' then b
else ''
end

b) you could create a group-column in the with-cte with case:

with ...(
select case :mySelectorVar
when '1' then a
when '2' then b
when '3' then a||b
else c
end as myCteGroupCol,
...
)
...
group by myCteGroupCol

In both cases you have the host variable :mySelectorVar to choose the grouping from RPG. But you have to make sure that every expression after THEN/ELSE "returns" the same data type.

The last solution ist using dynamic SQL and building the statement in a string. That's also not so complicated - but somehow many programmers don't like it.

It depends on your personal style and preferences, which solution to choose.

HTH and kind regards,
Daniel

Am 01.09.2023 um 23:29 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

A view's a good idea...

Or a UDTF.

Charles

On Fri, Sep 1, 2023 at 3:27 PM Mark Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

Dave,
Put the complex select statement into an SQL VIEW.
Then, just use:
SELECT * FROM viewname ORDER BY x, y, z as needed.
Hope that helps,
Mark S. Waterbury
On Friday, September 1, 2023 at 05:11:42 PM EDT, Dave <
dfx1@xxxxxxxxxxxxxx> wrote:

Sorry, I wasn't at all clear and have now changed to the RPG list as
suggested by David

Program source is SQLRPGLE
Depending on criteria provided by a user entry program, I only need
to (at the moment) execute the same SQL statement but with different
GROUP BY clauses depending on what the user enters.

So I'd have one common select statement (which is quite complicated,
I used the CTE because I found it easier to read) and a certain
number of different GROUP BY clauses :
eg,

Select columns from file
Group by a

Select columns from file
Group by a, b

Select columns from file
Group by c

I'm thinking I'd be better off copying and pasting the same code
rather than building the statement at run time.

Thanks

On Fri, 1 Sept 2023 at 15:08, Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Hi,



I have an RPGSQLE programme and I want to code several similar

statements.

They have a common WITH expression and only a difference in a GROUP
BY clause :



Exec sql

With t as (

SELECT etc with the same host
variables

)

SELECT etc

FROM t

GROUP BY (clause1 or clause2 ….)

End-exec



What is the best way to code this ? I seem to remember that having

several

copies of the statements instead of building them during run time is
actually more efficient.

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.


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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.