× 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.



but can you execute it with Query/400 or directly download into Excel?
Who wants to do that? HAHA

With the example that I provided, no! But make some minor changes to it
and the answer would be yes. As I said, if you can't figure it out, it
just might be easier to use I/O. That being said, I understand the
unwillingness to do so because of the hoops the op has to go through.




--

Michael Schutte
Admin Professional



Announcing Bob Evans Bob-B-QÂ Road Trip! For a limited time, America's best
Bob-B-QÂ tastes are all at Bob Evans! For more information, visit
www.bobevans.com/menu/seasonal.aspx



midrange-l-bounces@xxxxxxxxxxxx wrote on 06/11/2010 09:46:54 AM:

Put it in a service program and still get what you want.
Sure you can call it now from RPG or C, but can you execute it with
Query/400 or directly download into Excel?

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 Michael_Schutte@xxxxxxxxxxxx
Gesendet: Friday, 11. June 2010 15:08
An: Midrange Systems Technical Discussion
Betreff: Re: SQL Join - all rows from any table for employee

Who says you cant encapsulate this code? Put it in a service
program and still get what you want.


--

Michael Schutte
Admin Professional



Announcing Bob Evans Bob-B-QÂ Road Trip! For a limited time,
America's best Bob-B-QÂ tastes are all at Bob Evans! For more
information, visit www.bobevans.com/menu/seasonal.aspx



midrange-l-bounces@xxxxxxxxxxxx wrote on 06/11/2010 04:14:27 AM:

Michael,

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
other
hand, as I see it, one of the main advantages of doing this kind of
solution
(Birgitta's SQL solution) is that you can define a particular business
rule
just once, encapsulate in a SQL view and have it accessible for
several 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
to
me :-) ).

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
the
heck of it...

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
best
Bob-B-QÂ tastes are all at Bob Evans! For more information, visit
www.bobevans.com/menu/seasonal.aspx





"Dennis Lovelady"
<iseries@lovelady
.com>
To
Sent by: "'Midrange Systems Technical
midrange-l-bounce Discussion'"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>

cc

06/10/2010 06:30
Subject
PM 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
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.



--
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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.