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

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].