Re: Most efficient SQL approach

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I can't think of anything off-hand...

The grouping/rollup/cube functions are nice for YTD, MTD, WTD stuff...

But for YTD and Prior-YTD, the individual CTEs (or views) are likely the
best bet...

Charles

On Fri, Mar 6, 2020 at 2:04 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> I'm not sure I understand how OLAP functions fit in.
>
> If I could get one CTE with Order totals current year-to-date and previous
> year-to-date by customer, and the same for Invoice Total by customer
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
> Birgitta Hauser
> Sent: Friday, March 06, 2020 1:04 PM
> To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: Most efficient SQL approach
>
> I'd use Common Tables Expressions in composition with OLAP Functions
>
> 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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> Wilburn
> Sent: Freitag, 6. März 2020 17:52
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Most efficient SQL approach
>
> I am rewriting an old RPG report using SQL (and SQL views I already have).
> The "report" generates a database table as output
>
> I need one row for each customer.  In each row I need columns for several
> sales figures that can be found in two other views.
> I have SQL views for the Customer, Invoices and Orders.
>
> I currently have it written where each sales figure is a separate
> sub-select.  Note that the "where" clause for each is slightly different.
>
> It seems like this could be written more efficiently... but I'm not sure
> what approach to take
>
> 1.      Use common table expressions?
>
> 2.      Create the output table, then use separate SQL expressions to
> insert
> each of the summed values?
>
> This is a simplified version of the code (for simplicity I've replaced the
> RPG variables with constant values).
>
> SELECT CUS.COM_NBR, CUS.CUST_NBR, CUS.CUST_NAME, (more here), (select
> coalesce(sum(opn_amt),0) from V_ORDERS where com_nbr=cus.com_nbr and
> ent_nbr=cus.ent_nbr and
>          ord_date>='2020-01-01' and ord_date<='2020-03-04') as BOOK_YTD,
> (select coalesce(sum(shp_amt),0) from V_INVOICES where com_nbr=cus.com_nbr
> and ent_nbr=cus.ent_nbr and
>          inv_date>='2020-01-01' and inv_date<='2020-03-04' )as NET_YTD,
> (select coalesce(sum(shp_amt),0) from V_INVOICES where com_nbr=cus.com_nbr
> and ent_nbr=cus.ent_nbr and
>          inv_date>='2019-01-01' and inv_date<='2019-03-04' ) as
> PREV_YR_TOTAL, (select coalesce(sum(shp_amt),0) from V_INVOICES where
> com_nbr=cus.com_nbr and ent_nbr=cus.ent_nbr and
>          inv_date>='2019-01-01' and inv_date<='2019-03-04' ) as
> NET_PREV_YTD, (select coalesce(sum(ord_amt),0) from V_ORDERSA where
> com_nbr=cus.com_nbr and ent_nbr=cus.ent_nbr and
>          ord_date>='2019-01-01' and ord_date<='2019-03-04') as
> BOOK_PREV_YTD
> from V_CUSTMSTR as CUS where CUS.COM_NBR='012';
>
> Thanks,
> Greg
> --
> 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@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> 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@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> 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@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com