× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. April 2018

Re: Need an SQL joining lesson

Need help.


I need to see the field names. How do I do that??


select table_schema, table_name

from qsys2.systablestat

where table_name = 'MIGOBJ_LIST'

order by table_schema, table_name;

Thomas

On Fri, Apr 13, 2018 at 2: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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.