|
midrange.com
Have you thought about creating a join logical, or SQL view, instead of
doing the join in the program? Then you can use the view, or join logical,
to be the external data structure.
For instance we have a new programmer who is working on his first programs.
I told him he could get all of his data out of one file. It is a join
logical I created to make Queries easier. And by looking at the DDS, you
can see how it simplified the process.
* ADDLFM FILE(MGR1499SID/ZDATAW) MBR(ZDATAW)
* DTAMBRS((SAFETYF/AUDIT (AUDIT))
* (SAFETYF/AUDITINCID (AUDITINCID))
* (MGR1499SID/LOCATION (LOCATION))
* (MGR1499SID/EMPNAM (EMPNAM))
* (SAFETYF/INCIDENT (INCIDENT))
* (SAFETYF/STATUSES (STATUSES))
* (MGR1499SID/EMPNAM (EMPNAM))
* (MGR1499SID/COMPNAME (COMPNAME))
* (SAFETYF/INCIDENTT (INCIDENTT))
* (MGR1499SID/EMPNAM (EMPNAM))
* (MGR1499SID/DIVISION (DIVISION))
* (MGR1499SID/EMPNAM (EMPNAM)))
* Modification log:
* 06/11/97 by R.Berendt, CCP GDS, LLC
* Added field TAKEACTION from INCIDENT. This field
* is the 'Action to be taken'
*
JDFTVAL
R ZDATAWR JFILE(AUDIT AUDITINCID +
LOCATION EMPNAM INCIDENT +
STATUSES EMPNAM COMPNAME +
INCIDENTT EMPNAM DIVISION +
EMPNAM)
J JOIN(1 2)
JFLD(AUDIT# AUDIT#)
JDUPSEQ(SEQUENCE#)
J JOIN(1 3)
JFLD(LOC LOC)
J JOIN(1 4)
JFLD(AUDITOR# EMPLY)
J JOIN(2 5)
JFLD(INCIDENT# INCIDENT#)
J JOIN(2 6)
JFLD(STATUS STATUS)
J JOIN(3 7)
JFLD(MANAGR EMPLY)
J JOIN(3 8)
JFLD(ENTITY COMP)
J JOIN(5 9)
JFLD(INCIDENTYP INCIDENTYP)
J JOIN(8 10)
JFLD(MANAGR EMPLY)
J JOIN(8 11)
JFLD(DIV DIV)
J JOIN(11 12)
JFLD(MANAGR EMPLY)
AUDIT# JREF(1)
LOC JREF(1)
LOCNAME JREF(3) RENAME(NAME)
LOCMGR JREF(3) COLHDG('Location' +
'Manager') RENAME(MANAGR)
LMNAME JREF(7) COLHDG('Location' +
'Manager') RENAME(NAME)
ENTITY JREF(3)
CMPMGR JREF(8) COLHDG('Company' +
'Manager') RENAME(MANAGR)
CMNAME JREF(10) COLHDG('Company' +
'Manager') RENAME(NAME)
DIV JREF(8)
CMPNAME JREF(8) COLHDG('Company') +
RENAME(DESC40)
DIVNAME JREF(11) COLHDG('Division') +
RENAME(DESC40)
DVMGR JREF(11) COLHDG('Division' +
'Manager') RENAME(MANAGR)
DVNAME JREF(12) COLHDG('Division' +
'Manager') RENAME(NAME)
DATEAUDIT 8S 0 JREF(1) +
EDTWRD(' / / ')
DATAUDMM I SST(DATEAUDIT 5 2)
DATAUDDD I SST(DATEAUDIT 7 2)
DATAUDYY I SST(DATEAUDIT 3 2)
AUDITOR# JREF(1) COLHDG('Auditor')
AUDNAME JREF(4) COLHDG('Auditor') +
RENAME(NAME)
DATEREPORT 8S 0 JREF(1) +
EDTWRD(' / / ')
DATREPMM I SST(DATEREPORT 5 2)
DATREPDD I SST(DATEREPORT 7 2)
DATREPYY I SST(DATEREPORT 3 2)
SEQUENCE# JREF(2)
INCIDENT# JREF(2)
INCIDENTYP JREF(5)
INTYPDESC JREF(9)
SHORTDESC JREF(5)
REGULATION JREF(5)
ACTION JREF(5)
PENALTYAMT JREF(5)
TAKEACTION JREF(5)
STATUS JREF(2)
STATUSDESC JREF(6)
PRIORITY JREF(2)
DATEDEADL 8S 0 JREF(2) +
EDTWRD(' / / ')
DATDEDMM I SST(DATEDEADL 5 2)
DATDEDDD I SST(DATEDEADL 7 2)
DATDEDYY I SST(DATEDEADL 3 2)
DATECLOSE 8S 0 JREF(2) +
EDTWRD(' / / ')
DATCLOMM I SST(DATECLOSE 5 2)
DATCLODD I SST(DATECLOSE 7 2)
DATCLOYY I SST(DATECLOSE 3 2)
COMMENTS JREF(2)
DOCID10 JREF(2)
K AUDIT#
Rob Berendt
==================
Remember the Cole!
"Sneddon, Jim"
<JSneddon@AllstateTick To:
"'RPG400-L@midrange.com'" <RPG400-L@midrange.com>
eting.com> cc:
Sent by: Subject: Embedded SQL and
Externally Described Data Structure
owner-rpg400-l@midrang
e.com
04/04/01 10:53 AM
Please respond to
RPG400-L
I've started using SQL in RPG and I can use a data structure based on the
file so that I don't have to hard code every field name in the file:
For Example:
D ResvhistR E DS ExtName( RESVHIST)
then I can open a cursor on the file RESVHIST with a SELECT * FROM RESVHIST
WHERE... blah blah blah
When I do a FETCH next from -cursor- into :ResvhistR I get all my field
names automatically without having to list every field under the ResvhistR
data structure and without having to name every field in the select
statement.
Is there some way to do this from a multiple-file join SQL statement? Like
SELECT *
FROM RESVHIST, SHWFIL, DETLHIST
WHERE ...blah blah blah
I tried to set up another data structure overlaid by the 2 or 3 files I
need
but the compiler says I can't have a data structure as a subfield of
another
data structure.
If I set up a data structure with all the fields I want hard-coded then it
works fine.... but I have to list every field name in the SELECT statement
and then have them in the same order in my data structure.
I just wondered if I could take advantage of the external definitions for
each file and not have to worry about defining every single field I need
with multiple files.
Thanks
Jim Sneddon
AS400 RPG Programmer
Allstate Ticketing
jsneddon@allstateticketing.com
www.showtickets.com
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
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.
