Re: Coding SQL in RPG

[Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>]

 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.