Re: Need an SQL joining lesson

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

I AM getting more records than I expect.

On Thu, Apr 12, 2018 at 4:37 PM, Paul Therrien <
paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

> This join stmt:
>
>     FROM DBMSTF.CUSRBTMST m
>     JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr
>     JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr
>
> ... can only work if there is a single record per customer number in each
> file.
>
> If there are multiple CUSRBTMST, SLSALL or CRDMEDTL per cusnr then you are
> going to get a lot more records than you expect.
>
>
> Paul
>
>
> On 2018-04-12 15:49, Jeff Crosby wrote:
>
> Not the greatest at SQL but have a project where I think it's a perfect fit
> so I want to figure this out.  It's ultimately going into an RPG program
> (as an INSERT statement), but the question at the moment is the SQL, not
> RPG.  That's why what you see below is a SELECT with stuff hardcoded.  I
> just want the right numbers first.
>
> Some customers are going to start getting rebates.  The rebate will a
> percent of (sales - credits) by item category for the month.  I want to
> insert into a file the following 5 fields:
>
> customer number
> YYYYMM
> category
> total sales for the month
> total credits for the month
>
> There is a file of customers and their rebate percents, the sales file
> (with the category field in it), and a credits file (with the category
> field in it).  There could be both sales and credits for a
> customer/category, only sales for a customer/category, or only credits for
> a customer/category.  In all 3 cases I want 1 record per customer/category.
>
> CUSRBTMST - rebates percents file
> SLSALL - sales file
> CRDMEDTL - credits file
>
> This is the SQL I have now:
>
> SELECT m.cusnr,
>                201802 AS YYYYMM,
>                COALESCE(s.catcd1, c.catcd1, ' ') AS category,
>                COALESCE(SUM(s.itnsa), 0) AS Sales,
>                COALESCE(SUM(c.cdtam), 0) AS Credits
>     FROM DBMSTF.CUSRBTMST m
>     JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr
>     JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr
>     WHERE s.INVDT BETWEEN '2018-02-01' AND '2018-02-28' AND
>                   c.DTWRTISO BETWEEN '2018-02-01' AND '2018-02-28'
>     GROUP BY m.CUSNR, s.catcd1, c.catcd1
>     ORDER BY m.CUSNR, s.catcd1, c.catcd1
>
> That's a copy/paste from ACS run SQL scripts that I formatted a bit
> further.
>
> The results I get have no resemblance to the actual sales and credits.  :)
>
> I'm joining the rebate percent file to both the sales and credits file.
> That might be the problem, I don't know.  Are 2 SELECTS required?
>
> Thanks for any pointers.
>
>
> --
>
>
> Jeff Crosby
> VP Information Systems
> UniPro FoodService/Dilgard
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> direct.dilgardfoods.com
>
> The opinions expressed are my own and not necessarily the opinion of my
> company.  Unless I say so.