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.