Re: Need an SQL joining lesson

The CTE: I will get there. At some point. :)


On Fri, Apr 13, 2018 at 1:16 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Look at the intermediate table...

SELECT m.cusnr,
201802 AS YYYYMM,
cat.ctcode AS category,
s.invdat,
s.itnsa AS Sales,
c.dtwrtiso,
c.cdtam AS Credits
FROM DBMSTF.CUSRBTMST m
CROSS JOIN DBMSTF.DMCATMST cat
LEFT JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr AND s.catcd1 =
cat.ctcode
LEFT JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr AND c.catcd1 =
cat.ctcode
WHERE s.INVDT BETWEEN '2018-02-01' AND '2018-02-28' AND
c.DTWRTISO BETWEEN '2018-02-01' AND '2018-02-28'
ORDER BY 1, 2

I suspect we're seeing a cross-join between sales & credits on (cusnr,
catcd1)

I was afraid of that yesturday, but thought of it after I'd signed off and
the tables I was testing with were in QTEMP :)

The CTE solution would work ;)

Charles


On Fri, Apr 13, 2018 at 10:45 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

I used cat.ctcode first. Then I thought maybe that was the problem, so I
switched to COALESCE(s.catcd1, c.catcd1, ' ') . Same results either

way.

I probably won't get to work on it anymore today. :(

On Fri, Apr 13, 2018 at 12:30 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

The CTE is actually easier to understand IMO...

As you can see what's built at each step...

But if you don't want to go the CTE route, how about the post without

it?

I see you added DMCATMST...but you're not using the ctcode column in

your

SELECT & GROUP BY

Try
SELECT m.cusnr,
201802 AS YYYYMM,
cat.ctcode AS category,
COALESCE(SUM(s.itnsa), 0) AS Sales,
COALESCE(SUM(c.cdtam), 0) AS Credits
FROM DBMSTF.CUSRBTMST m
CROSS JOIN DBMSTF.DMCATMST cat
LEFT JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr AND s.catcd1 =
cat.ctcode
LEFT JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr AND c.catcd1 =
cat.ctcode
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, cat.ctcode
ORDER BY 1, 2


Charles

On Fri, Apr 13, 2018 at 9:57 AM, Jeff Crosby <

jlcrosby@xxxxxxxxxxxxxxxx>

wrote:

Charles

Did you see my other posts?

With the CTE? Yea. Trying to avoid that. If it's that complex I

may

not

go the SQL route at all. When I look at it a year from now I won't
remember . . .

My issue is my job is more corporate management, by far, than tech

stuff.

Don't get enough immersion in it.


On Fri, Apr 13, 2018 at 11:32 AM, Charles Wilt <

charles.wilt@xxxxxxxxx

wrote:

On Fri, Apr 13, 2018 at 7:51 AM, Jeff Crosby <

jlcrosby@xxxxxxxxxxxxxxxx>

wrote:

I get these results:

Cust YYYYMM Category Sales Credits
==== ====== ======== ======= =======
1892 201802 E 6967.72 8400.20
1892 201802 F 3540.22 5097.12
4554 201802 B 3556.01 3406.56
4554 201802 D 1004.97 163.76
4554 201802 E 5706.34 6344.00
(Total) 20775.26 23411.64

I note a few things:

1) more credit than sales, never a good thing from a business

standpoint

:)

2) numbers obviously not right

3) if there are sales and no credits, no record appears and I

assume

the

reverse would be true

​As expected, the fan out is the problem...

Did you see my other posts?

Charles​
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--


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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--


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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD