Conference ilbbak::ibi_focus

358.0. "RDB Multi-Table definition question" by KAHALA::STEVENS () Mon Aug 06 1990 12:30

    I'd like some help in creating a MAS and ACX file for a RDB database
    that I need to access. I want to combine two separate tables so I
    can gather summary information from both tables in one pass through
    FOCUS.      
    
    I can get the results if I collect the data from each table separately,
    and then merge the two together, but I'd like to simplify the process.
    
    The structure I'm having a problem with has two tables one current
    and one historical, both of which is similar in fields, but contain
    different data. One table, HEADER has the current order information
    stored in it. The other table, ARCHDR has closed/cancelled orders
    stored. What I really want to do is to combine the two tables to look
    like one, in order to gather summary and detail information for
    fiscal timeframes.
    
    Is it possible to define a single MAS and ACX to access the two
    tables? If so, what would the MAS and ACX look like. Does it need
    a dummy root level defined?
    
    The tables look like the following :
       
    ORD1001.MAS
    -----------
    FILE=ORD1001, SUFFIX=RDB,$
    SEGNAME=HEADER, SEGTYPE=S0,$                            
    FIELD=DEC_NO    ,ALIAS=DEC_NO   ,USAGE=A9	,A9	,MISSING=ON,$
    FIELD=SOLD_CUST ,ALIAS=SOLD_CUST,USAGE=A7	,A7	,MISSING=ON,$
    .
    .
    .
    
    ORD1001.ACX
    -----------
    SEGNAME=HEADER, TABLENAME="ORD1001.HEADER",$
    
    
    ORD1004.MAS
    -----------
    FILE=ORD1004, SUFFIX=RDB,$
    SEGNAME=ARCHDR, SEGTYPE=S0,$
    FIELD=DEC_NO    ,ALIAS=DEC_NO   ,USAGE=A9   ,A9     ,MISSING=ON,$
    FILED=SOLD_CUST ,ALIAS=SOLD_CUST,USAGE=A7   ,A7     ,MISSING=ON,$
    .
    .
    .
    
    ORD1004.ACX
    -----------
    SEGNAME=ARCHDR, TABLENAME="ORD1004.ARCHDR",$
    
    
    I'd appreciate any help you can give.
    
    Paul

    What is the Join relationship between these two tables?

    There is no real JOIN relationship. 
    
    The records in both tables have the same fields but have totally 
    unique records. What is really needed here is to merge the two
    files together.
    
    The only solution we have so far is to extract each table via hold
    files to external RMS sequential files. We merge the two RMS files,
    then we fixform the merged file back into FOCUS for reporting needs. 
    
    We now have someone in our group playing with the MATCH function to
    see if there is an easy want to accomplish the same results as our
    current solution explained above!
    
    Regards.