Re: keys on 2 joined files?

If I have these three tables:
CREATE TABLE ROB/CMAST (JOINFLD01 CHAR (5 ) NOT NULL WITH DEFAULT, 
DATAFLD01 CHAR (10 ) NOT NULL WITH DEFAULT) 

CREATE TABLE ROB/CRMAST (JOINFLD01 CHAR ( 5) NOT NULL WITH DEFAULT,
JOINDFLD02 CHAR (2 ) NOT NULL WITH DEFAULT, DATAFLD02 CHAR (20 ) 
NOT NULL WITH DEFAULT) 

CREATE TABLE ROB/CNMAST (JOINDFLD02 CHAR ( 2) NOT NULL WITH 
DEFAULT, DATAFLD03 CHAR (30 ) NOT NULL WITH DEFAULT) 

With this data:
INSERT INTO ROB/CMAST VALUES('A', 'TEST') 
1 rows inserted in CMAST in ROB. 
INSERT INTO ROB/CRMAST VALUES('A', 'B', 'ALPHA')
1 rows inserted in CRMAST in ROB. 
INSERT INTO ROB/CNMAST VALUES('B', 'BRAVO') 
1 rows inserted in CNMAST in ROB. 

I can do this:
SELECT CMAST.JOINFLD01, CMAST.DATAFLD01, 
       CRMAST.JOINDFLD02, CRMAST.DATAFLD02, 
       CNMAST.DATAFLD03 
FROM ( CMAST 
       JOIN CRMAST ON CMAST.JOINFLD01=CRMAST.JOINFLD01) 
       JOIN CNMAST ON CRMAST.JOINDFLD02=CNMAST.JOINDFLD02
ORDER BY CMAST.DATAFLD01, CRMAST.DATAFLD02 
JOINFLD01  DATAFLD01   JOINDFLD02  DATAFLD02             DATAFLD03
  A        TEST            B       ALPHA                 BRAVO 

Now, I could create a logical file, (but without the sort) with this:
CREATE VIEW ROB/CVIEW 
       (JOINFLD01, DATAFLD01, JOINDFLD02, DATAFLD02, DATAFLD03)
AS 
SELECT CMAST.JOINFLD01, CMAST.DATAFLD01, 
       CRMAST.JOINDFLD02, CRMAST.DATAFLD02, 
       CNMAST.DATAFLD03 
FROM ( CMAST 
       JOIN CRMAST ON CMAST.JOINFLD01=CRMAST.JOINFLD01) 
       JOIN CNMAST ON CRMAST.JOINDFLD02=CNMAST.JOINDFLD02 
View CVIEW created in ROB. 

Then my queries would be as simple as:
SELECT JOINFLD01, DATAFLD01, 
       JOINDFLD02, DATAFLD02,
       DATAFLD03 
FROM CVIEW 
ORDER BY DATAFLD01, DATAFLD02

It would be sweet if it allowed:
CREATE VIEW ROB/CVIEW 
       (JOINFLD01, DATAFLD01, JOINDFLD02, DATAFLD02, DATAFLD03)
AS 
SELECT CMAST.JOINFLD01, CMAST.DATAFLD01, 
       CRMAST.JOINDFLD02, CRMAST.DATAFLD02, 
       CNMAST.DATAFLD03 
FROM ( CMAST 
       JOIN CRMAST ON CMAST.JOINFLD01=CRMAST.JOINFLD01) 
       JOIN CNMAST ON CRMAST.JOINDFLD02=CNMAST.JOINDFLD02 
ORDER BY CMAST.DATAFLD01, CRMAST.DATAFLD02

But SQL insists on storing the indexes seperate from the data, (except for 
primary keys).  I suppose you could run the statements a few times under 
debug to see what, if any, indexes you should build.

And since this is a RPG list, (and not MIDRANGE-L) let's stick the word 
RPG in here.  You do know how to process a cursor with imbedded SQL, eh? 
If not, samples are available upon request.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




WyvernGod@xxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/17/2003 09:35 AM
Please respond to RPG programming on the AS400 / iSeries
 
        To:     rpg400-l@xxxxxxxxxxxx
        cc: 
        Fax to: 
        Subject:        keys on 2 joined files?


Hello new guy here with a migraine starting problem

OK here is my problem.. i need to have a logical of 3 joined files ... the 

contract master, client relation master, and the client master. the 
problem 
is the contract and client master bother have something i need to sort 
with. 
In contract i need the company code and employee location, then the client 

name from client master, then the policy number from contract. every thing 
i 
try comes back in error and suggestions on how I can do this?  I'm trying 
to 
have these come out by name but in order for the program to work they myst 

also be sorted by the employee location.

Thanks,
Chris