Thanks Simon. I might go try this again and maybe send out the exact source. The only difference I see is that I was joining 1 with 2 and 2 with 3. Your example shows joining 1 with 2 and 1 with 3
In terms of your example files and the way the data is...
R EMPFMT3 JFILE(MIKE_EMP2 MIKE_EMP1 MIKE_EMP2)
J JOIN(1 2)
JFLD(EMPID EMPID )
J JOIN(2 3)
JFLD(SVSRID EMPID)
I did mine the other way because I only wanted records that existed in your EMP2 file as that has current employees only and only those with a userid, EMP1 has current and every employee who use to work here. And I also needed to key the file based on a field in your EMP2 file (the person userid ) so I had to list that first in DDS in order to do that.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Simon Coulter
Sent: Tuesday, March 04, 2008 7:50 PM
To: Midrange Systems Technical Discussion
Subject: Re: Join Logical file using the same physical file twice
On 05/03/2008, at 4:12 AM, Mike Cunningham wrote:
I have a need to join two files as if they were three. We have an
employee file that has an employee ID and an employees supervisors
smeployee ID. We have a second file that is part of our menu system
that has an employees employee id and their system userid and e-
mail address. I want to create a single join file that has the
employee's userid and e-mail and the supervisors userid and e-mail.
I can't do this
R EMFLE JFILE(UTPUNAME LWPEMFLE UTPUNAME)
J JOIN(1 2)
JFLD(USS# LWPCTID)
J JOIN(2 3)
JFLD(LWEMPS USS#)
The create fails saying there are two many "J" specs. Acts like the
second UTPUNAME is being ignored.
Don't know why you got this error. DDS LF supports joining a file to
itself. See below.
So I tried creating an alias pointing to UTPUNAME named UTAUNAME
and tried this..
R EMFLE JFILE(UTPUNAME LWPEMFLE UTAUNAME)
J JOIN(1 2)
JFLD(USS# LWPCTID)
J JOIN(2 3)
JFLD(LWEMPS USS#)
This create failed because it said DDM file UTAUNAME could not be
accessed at remote location *LOCAL. We do have a *LOCAL defined
under WRKRDBDIRE that net.data needed that points to the local
system name. (Interesting that the SQL CREATE ALIAS used DDM files).
Can anyone offer any suggestions on how to fix the DDM issue or how
to setup the kind of join I want to create?
Don't know about the DDM issue. I consider that the wrong approach.
Either do the join in DDS, do the join in a VIEW, or do the join in
the SELECT statement.
p.s. The app that needs this is doing a JDBC connection from a PC
and using SQL commands to access the data and could do two SELECT
commands but I would prefer to keep the logic for how this join is
defined and the fields it contains and what the application sees on
the iSeries side and not in the PC application.
You could create a VIEW as others have shown. You could specify the
join directly on the SELECT statement from the client (you do NOT
need two SELECT statements). You could get the join correct in the
DDS. Actually, I don't like any of these approaches because you are
giving the JDBC client direct access to your database. I consider
that a wrong approach and limiting clients to calling stored
procedures is a better approach.
Anyway, just to prove it can be done:
MIKE_EMP1:
*************** Beginning of data
**********************************************
UNIQUE
R EMPFMT1
EMPID 5P 0
SVSRID 5P 0
K EMPID
****************** End of data
*************************************************
MIKE_EMP2:
*************** Beginning of data
**********************************************
UNIQUE
R EMPFMT2
EMPID 5P 0
EMPUSRPRF 10A
EMPEMAIL 30A
K EMPID
****************** End of data
*************************************************
runqry *n mike_emp1
Line ....+....1....+.
EMPID SVSRID
000001 1 2
000002 2 3
000003 3 0
****** ******** End of report ********
runqry *n mike_emp2
Line ....+....1....+....2....+....3....+....4....
EMPID EMPUSRPRF EMPEMAIL
000001 1 FREDNURK fred.nurk@xxxxxxxxxxx
000002 2 JOEBLOW joe.blow@xxxxxxxxxxx
000003 3 BOSS boss@xxxxxxxxxxx
****** ******** End of report ********
MIKE_EMPLF
*************** Beginning of data
**********************************************
JDFTVAL
R EMPFMT3 JFILE(MIKE_EMP1
MIKE_EMP2 MIKE_EMP2)
J JOIN(1 2)
JFLD(EMPID EMPID )
J JOIN(1 3)
JFLD(SVSRID EMPID)
* EMPID JREF(1)
* COLHDG('EMPLOYEE' 'ID')
EMPUSRPRF JREF(2)
COLHDG('EMPLOYEE'
'PROFILE')
EMPEMAIL JREF(2)
COLHDG('EMPLOYEE' 'E-MAIL')
* SVSRID JREF(1)
* COLHDG('SUPERVISOR' 'ID')
SVSRUSRPRF RENAME(EMPUSRPRF) JREF(3)
COLHDG('SUPERVISOR'
'PROFILE')
SVSREMAIL RENAME(EMPEMAIL) JREF(3)
COLHDG('SUPERVISOR' 'E-
MAIL')
****************** End of data
*************************************************
runqry *n mike_emplf
Line ....+....1....+....2....+....3....+....4....+....5....+....
6....+....7....+.
EMPLOYEE EMPLOYEE SUPERVISOR
SUPERVISOR
PROFILE E-MAIL PROFILE E-MAIL
000001 FREDNURK fred.nurk@xxxxxxxxxxx JOEBLOW
joe.blow@xxxxxxxxxxx
000002 JOEBLOW joe.blow@xxxxxxxxxxx BOSS
boss@xxxxxxxxxxx
000003 BOSS boss@xxxxxxxxxxx
****** ******** End of report ********
Taa daa! This appears to satisfy your requirements:
"I want to create a single join file that has the employee's userid
and e-mail and the supervisors userid and e-mail."
FREDNURK reports to JOWBLOW. JOEBLOW reports to BOSS. BOSS reports
to ... well no-one really ...
Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists
http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------
--
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.
midrange.com
