Re: Need an SQL joining lesson

Just add the insert at the top..

insert into dbmstf.cusrbtsls
WITH sales
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7) AS YearMonth,
SUM(itnsa) AS total
FROM slshst.slsall
WHERE invdt BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7))
, credits
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7) AS YearMonth,
SUM(cdtam) AS total
FROM dbmstf.crdmedtl
WHERE dtwrtiso BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7))
, custs_cats
AS (SELECT cst.cusnr, cat.ctcode, '2018-02' AS YearMonth
FROM dbmstf.cusrbtmst cst CROSS
JOIN dbmstf.dmcatmst cat)
SELECT cc.cusnr, cc.ctcode, cc.YearMonth , COALESCE(sls.Total, 0)
AS sales_total , COALESCE(crd.total, 0)
AS credit_total FROM custs_cats cc LEFT OUTER JOIN sales sls ON
cc.cusnr = sls.cusnr AND
cc.ctcode = sls.catcd1
AND
cc.YearMonth =
sls.YearMonth LEFT OUTER JOIN
credits crd ON cc.cusnr =
crd.cusnr AND
cc.ctcode = crd.catcd1
AND
cc.YearMonth =
crd.YearMonth
WHERE sls.total > 0 OR
crd.total > 0
ORDER BY 1, 2;

Charles

On Mon, Apr 16, 2018 at 8:32 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

I am now trying to convert the SQL we built on Friday into an INSERT.

WITH sales
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7) AS YearMonth,
SUM(itnsa) AS total
FROM slshst.slsall
WHERE invdt BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7))
, credits
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7) AS YearMonth,
SUM(cdtam) AS total
FROM dbmstf.crdmedtl
WHERE dtwrtiso BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7))
, custs_cats
AS (SELECT cst.cusnr, cat.ctcode, '2018-02' AS YearMonth
FROM dbmstf.cusrbtmst cst CROSS
JOIN dbmstf.dmcatmst cat)
INSERT INTO DBMSTF.CUSRBTSLS (cc.*)
SELECT cc.cusnr, cc.ctcode, cc.YearMonth , COALESCE(sls.Total, 0)
AS sales_total , COALESCE(crd.total, 0)
AS credit_total FROM custs_cats cc LEFT OUTER JOIN sales sls ON
cc.cusnr = sls.cusnr AND
cc.ctcode = sls.catcd1
AND
cc.YearMonth =
sls.YearMonth LEFT OUTER JOIN
credits crd ON

cc.cusnr =

crd.cusnr AND
cc.ctcode = crd.catcd1
AND
cc.YearMonth =
crd.YearMonth
WHERE sls.total > 0 OR
crd.total > 0
ORDER BY 1, 2;

The syntax checker barfs at the INSERT, saying it's unexpected. If I

put a

comma in front of INSERT, then it says INTO is unexpected.

I googled and the results I get seem to be all over the board. Some have
the INSERT at the beginning, some at the end, and some after the CTE

setup

just before the last SELECT above (which is where I put my INSERT).

Is that the right spot for the INSERT? If not, where?

And, I'm guessing, if the first 2 fields are PK (they are), then I don't
need the ORDER BY? (I will later be reading with a cursor.)

Thanks.


On Fri, Apr 13, 2018 at 4:27 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

I took the SQL you posted many posts ago, put it into ACS SQL, fixed

some

syntax errors, and ended up with this:

WITH sales
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7) AS YearMonth,
SUM(itnsa) AS total
FROM slshst.slsall
WHERE invdt BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(invdt), 1, 7))
, credits
AS (SELECT cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7) AS

YearMonth,

SUM(cdtam) AS total
FROM dbmstf.crdmedtl
WHERE dtwrtiso BETWEEN '2018-02-01' AND '2018-02-28'
GROUP BY cusnr, catcd1, SUBSTR(CHAR(dtwrtiso), 1, 7))
, custs_cats
AS (SELECT cst.cusnr, cat.ctcode, '2018-02' AS YearMonth
FROM dbmstf.cusrbtmst cst CROSS
JOIN dbmstf.dmcatmst cat)
SELECT cc.cusnr, cc.ctcode, cc.YearMonth , COALESCE(sls.Total, 0)
AS sales_total , COALESCE(crd.total, 0)
AS credit_total FROM custs_cats cc LEFT OUTER JOIN sales sls ON
cc.cusnr = sls.cusnr AND
cc.ctcode =

sls.catcd1

AND
cc.YearMonth =
sls.YearMonth LEFT OUTER JOIN
credits crd ON

cc.cusnr

= crd.cusnr AND
cc.ctcode =

crd.catcd1

AND
cc.YearMonth =
crd.YearMonth
ORDER BY 1, 2;

Which produces the correct results.

However -

It included those with no sales/credits and wasn't in sequence, so I

added

WHERE sls.total > 0 OR crd.total > 0
ORDER BY 1, 2;

and got exactly what I want.

You're a genius Charles!

Thanks!




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

It really pretty easy...

Just think of of CTE as building a temporary table with the results of

the

select....

What's nice, is that as you build, you can always put in a SELECT *

FROM

CTE to see what you've got

with sales as (
select cusnr, catcd1, substr(char(invdt),1,7) as YearMonth
sum(itnsa) as total
from slshst.slsall
where invdat between '2018-02-01' AND '2018-02-28'
group by cusnr, catcd1, substr(char(invdt),1,7)
)
select * from sales;


Now add the next one

with sales as (
select cusnr, catcd1, substr(char(invdt),1,7) as YearMonth
sum(itnsa) as total
from slshst.slsall
where invdat between '2018-02-01' AND '2018-02-28'
group by cusnr, catcd1, substr(char(invdt),1,7)
), credits as (
select cusnr, catcd1, substr(char(dtwrtiso),1,7) as YearMonth
sum(cdtam) as total
from dbmstf.crdmedtl
where dtwrtiso between '2018-02-01' AND '2018-02-28'
group by cusnr, catcd1, substr(char(dtwrtiso),1,7)
select * from credits;


Even after you've got the whole thing done, you can go back and add

the

SELECT * FROM CTE; anywhere and ignore the rest of the statement...

Charles




On Fri, Apr 13, 2018 at 12:25 PM, Jeff Crosby <

jlcrosby@xxxxxxxxxxxxxxxx>

wrote:

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

--


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.

--


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