RE: SQL Join - all rows from any table for employee

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

Brilliant!  Yes, I see why that would work, and I'll test the concept as
soon as I get to work!  Very nice, thank you!

I am also surprised by the syntax on the CTE named Z.  Looks like the syntax
for a View; didn't know that could be done, but I like it.

Thanks again, Birgitta!
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Politics is not a bad profession. If you succeed there are many rewards, if
you disgrace yourself you can always write a book."
        -- Ronald Reagan 


> Here is an example (a little simplified), just to see if I understood
> clearly:
> 1. Table PRMAST:
> Create Table Qtemp/PRMAST
> (employee Integer,
>  name Varchar(50));
>
> Insert into PRMAST
> Values (1, 'Hauser'), (2, 'Meier'), (3, 'Schmidt'), (4, 'Huber'), (5,
> 'Fischer');
>
> Results in:
> Empl  Name
> 1	Hauser
> 2	Meier
> 3	Schmidt
> 4	Huber
> 5	Fischer
>
> 2. Table DEDBAL
> Create Table Qtemp/DEDBAL
> (employee Integer,
>  DedAmount Dec(11, 2),
>  DedOther  Dec(11, 2));
>
> insert into DEDBAL
> Values (1, 123,45, 100,00), (3, 555,20, 700,3), (4, 222,0, 200,0);
>
> Results in:
> Empl	DedAmount	DedOther
> 1	123,45	100,00
> 3	555,20	700,30
> 4	222,00	200,00
>
> 3. Table INCBAL
> Create Table QTEMP/INCBAL
> (employee Integer,
>  IncAmount  Dec(11, 2),
>  IncOther   Dec(11, 2));;
>
> Insert into Incbal
> Values (2, 100,45, 300,22), (3, 200,2, 0), (4, 100,3, 0);
>
> Results in:
> Empl	IncAmount	IncOther
> 2	100,45	300,22
> 3	200,20	0,00
> 4	100,30	0,00
>
> Now let's put all together:
> With x as (Select Employee From DEDBAL
>            Union
>            Select Employee From INCBAL),
>      y as (select p.*
>               from  x join prmast p on x.employee = p.employee),
>      z (Employee, Name, DedAmount, DedOther, IncAmount, IncOther)
>         as (Select y.employee, name, DedAmount, DedOther, 0, 0
>              From y left outer join DedBal d on y.employee = d.employee
>             Union
>             Select y.employee, name, 0, 0, IncAmount, IncOther
>             From y left outer join IncBal i on y.employee = i.employee)
> Select Employee, Name,
>        Sum(DedAmount), Sum(DedOther), Sum(IncAmount), sum(IncOther)
> > From z
> Group by Employee, Name;;
>
> The query above results in:
> Empl	Name		DedAmount	DedOther	IncAmount
> IncOther
> 1	Hauser	123,45	100,00	0,00		0,00
> 2	Meier		0,00		0,00		100,45	300,22
> 3	Schmidt	555,20	700,30	200,20	0,00
> 4	Huber		222,00	200,00	100,30	0,00
>
> CTE x: Determines all employees that are either in DEDBAL or INCBAL
> CTE y: Joins the first CTE with PRMAST, to get all information needed
> from
> PRMAST for the selected employees
> CTE z: Joins CTE y with DEDBAL and INCBAL and combines the results in a
> single table
> Final select summarizes the results from z
>
> BTW UNION and UNION DISTINCT is the same, i.e. duplicates are ignored
> If you need all rows from both tables you need to specify UNION ALL
>
> @Eric: Common Table Expressions are NOT temporary tables, but temporary
> views that are only available for the current query.
>
> 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!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
> Gesendet: Friday, 11. June 2010 00:31
> An: 'Midrange Systems Technical Discussion'
> Betreff: RE: SQL Join - all rows from any table for employee
>
> Yeah, I was pretty loose about the specifics because I don't want to
> give
> the impression that I want someone else to write this for me.  I don't.
> But
> here we go:
>
> DEDBAL contains quarterly deduction amounts (employer and employee
> contribution), the year, the quarter, employee number and other stuff.
>
> INCBAL contains quarterly income amounts, Federal wage base, the year,
> the
> quarter, employee and other stuff.
>
> I need employee name, year, quarter, max(wage base), sum(income
> amounts),
> sum(employee contribution), sum(employer contribution) and some other
> stuff.
>
> So from these three tables, I need multiple columns of each, put
> together
> onto a single row by employee.
>
> UNION might work, but I don't think UNION DISTINCT (by itself) will do
> what
> I want.  I could do a left outer and an exception join, but then the
> summing
> and other details would get complex and would need to be mostly (but
> not
> completely) duplicated.  Messy.
>
> I liked the look of FULL JOIN, but apparently that's only available
> some
> after V5R3, which is where we are.
>
> Likely I'll just write a program, but I wondered if there might be a
> more
> standard solution.
>
> Sorry I  wasn't completely upfront before.  I just don't want to be one
> of
> those "here's my task - do it for me" people.
>
> Dennis Lovelady
> http://www.linkedin.com/in/dennislovelady
> --
> "A cynic is a man who knows the price of everything and the value of
> nothing."
>         -- Oscar Wilde
>
> > Do you need info from DEDBAL and INCBAL? If not, you could use
> EXISTS.
> > SELECT *  <-- desired fields from PRMAST go here
> > FROM PRMAST
> > WHERE EXISTS (SELECT 1 FROM DEDBAL WHERE DEDEMP=PREMP)
> >    OR EXISTS (SELECT 1 FROM INCBAL WHERE INCEMP=PREMP)
> >
> >
> > If you need to pull data from DEDBAL and INCBAL, you could use a LEFT
> > OUTER
> > join
> > and then exclude those cases where an employee has no records in
> DEDEMP
> > and
> > INCEMP.
> >
> > Try this:
> >
> > SELECT *  <-- desired fields from PRMAST, DEDBAL, and INCBAL go here
> > FROM PRMAST
> > LEFT OUTER JOIN DEDBAL ON DEDEMP=PREMP
> > LEFT OUTER JOIN INCBAL ON INCEMP=PREMP
> > WHERE DEDEMP IS NOT NULL OR INCEMP IS NOT NULL
> >
> > In this case, if an employee has records in DEDBAL but not in INCBAL,
> > any
> > fields in INCBAL would be null and vice versa.
> >
> > Have fun!
> >
> > Richard Casey
> >
> >
> >
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
> > Sent: Thursday, June 10, 2010 3:38 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: SQL Join - all rows from any table for employee
> >
> > We have three tables:  DEDBAL, INCBAL, and PRMAST.
> >
> >
> >
> > Each of these contains an employee number (DEDEMP, INCEMP, PREMP).
> We
> > want
> > to produce output where an employee is represented in either DEDBAL
> and
> > INCBAL or both.  (For the sake of argument, we can assume that PRMAST
> > is
> > present for each employee).
> >
> >
> >
> > I've tried various types of joins (LEFT OUTER, FULL, et cetera) and
> > have
> > pored through the archives and GOOGLE and come up empty on a means of
> > doing
> > this exactly right.  (I'm convinced this is a common issue; so my
> issue
> > is
> > probably my choice of search words - usually resulting in too many
> > hits).
> > Would some kind soul mind directing me to guidelines on how to write
> > the
> > JOIN for this challenge?
> >
> >
> >
> > Thanks!
> >
> >
> >
> > Dennis E. Lovelady
> > AIM/Skype: delovelady      MSN: fastcounter@xxxxxxxxxxxx
> >  <http://www.linkedin.com/in/dennislovelady>
> > www.linkedin.com/in/dennislovelady --
> > I am at one with my duality.
> >
> >
> > --
> > 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: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
>
> --
> 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: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> 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: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.