|
Michael,other
Sometimes it just might be easier to write the program with I/O.
Yes, of course. As always, it depends on your business needs. On the
hand, as I see it, one of the main advantages of doing this kind ofsolution
(Birgitta's SQL solution) is that you can define a particular businessrule
just once, encapsulate in a SQL view and have it accessible for severalto
developers and/or programs, queries, etc., without having to reinvent the
wheel every time (by nature, I am lazy, so this kind of solutions appeal
me :-) ).the
Also, there is what I suspect is the main interest in this kind of
solutions: Just to see if and how can be done. In other words, just for
heck of it...best
Best Regards,
Luis Rodriguez
IBM Certified Systems Expert â eServer i5 iSeries
On Fri, Jun 11, 2010 at 7:49 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:
Sometimes it just might be easier to write the program with I/O.
DoW not %EoF(PRMAST);
Read PRMAST;
If %EoF(PRMAST);
Leave;
EndIf;
Chain (empno) DEDBAL;
If not %Found();
// Clear fields;
employee_contributions = 0;
employer_contributions = 0;
EndIf;
Wages = 0;
SetLL (empno) INCBAL;
Dow not %EoF(INCBAL);
Read INCBAL;
If %EoF();
Leave;
EndIf;
// sum wages
Wages += income_amount;
EndDo;
// Print employee data
EndDo;
--
Michael Schutte
Admin Professional
Announcing Bob Evans Bob-B-QÂ Road Trip! For a limited time, America's
ToBob-B-QÂ tastes are all at Bob Evans! For more information, visit
www.bobevans.com/menu/seasonal.aspx
"Dennis Lovelady"
<iseries@lovelady
.com>
ccSent by: "'Midrange Systems Technical
midrange-l-bounce Discussion'"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
Subject
06/10/2010 06:30
givePM RE: SQL Join - all rows from any
table for employee
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
Yeah, I was pretty loose about the specifics because I don't want to
thethe 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,
amounts),quarter, employee and other stuff.
I need employee name, year, quarter, max(wage base), sum(income
togethersum(employee contribution), sum(employer contribution) and some other
stuff.
So from these three tables, I need multiple columns of each, put
whatonto a single row by employee.
UNION might work, but I don't think UNION DISTINCT (by itself) will do
notI 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
somecompletely) duplicated. Messy.
I liked the look of FULL JOIN, but apparently that's only available
moreafter V5R3, which is where we are.
Likely I'll just write a program, but I wondered if there might be a
ofstandard solution.
Sorry I wasn't completely upfront before. I just don't want to be one
EXISTS.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
DEDEMP
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
Weand
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).
andwant
to produce output where an employee is represented in either DEDBAL
issueINCBAL 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
mailingis
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)
listlist
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
listTo 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
listTo 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
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.
As an Amazon Associate we earn from qualifying purchases.
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.