Re: SQL Join - all rows from any table for employee -- MIDRANGE-L

P.S. May I add (quite off topic) that your business signature sometimes
should be banned? Depending on how near is one's time for lunch/dinner

they

can be quite tormenting :-)

That's why I use them. Drive sales baby! HAHA.

Anyway, the OP was having an issue with the SQL statement so my suggestion
was to write a program. Sometimes its just easier. But again who says you
can't encapulate business rules with service programs. It doesn't always
have to be SQL. In the example I posted, you could easily change to two
different procedures and to accept an employee number and have it return a
value. Then you can create a SQL UDF to call the service program. Then
you can write an sql statement as such.

Select employee, departement, GetContributions(employee), GetWageBase
(employee)
from PRMAST
WHERE department = "I/T"
Order by 4

But I understand not wanting to right the program because of the hoops that
he would have 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 05:43:36 AM:

Michael,

I don't really look at SQL as a way of encapsulating code, but as a way
of encapsulating business rules. It does not matter if you need one

record

or one million, one field or many, RPG, QRY, QM, ODBC, etc. A well

described

SQL structure works for everything, at the most basic of levels.

That said, it depends, as I wrote before, on your business needs
(performance, kind of programs, etc), so the mileage can (and do) vary.

As I

see it, in the programming world, as in real life, there are not unique,
work for all, solutions.

Best Regards,

Luis Rodriguez

On Fri, Jun 11, 2010 at 8:38 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:

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

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

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

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

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.

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