I have done a picture to view the structure and retrieval order or a report I am using. I am a little confused on exactly how to read it though it order to troubleshoot problem spots and how to fix them. I found some stuff in the documentation but am still a little confused about what some of the codes mean: I U , I KM, I KLU.......

Thanks,
Malinda

This message has been edited. Last edited by: Kerry,

Retrieval order is generally top to bottom, left to right. So it will follow the path to the bottom of one leg then go back up until it finds a sibling then follow that path down, etc.

The I means the column is indexed which means it can be the target of a join.

A U means that the segment is unique, that is for each and every occurrence of its parent, there can be only one (like the Highlander). It doesn't mean that there has to be one, just that each parent can have only one. Unique segments are considered part of the parent when talking about retrieval and are not considered a separate path.

KM meand keyed multiple. This is related to a child segment where there can be more than one child per parent.

KLM and KLU are a bit sophisticated and related to a logical link described inside a master. Some of the demo files are described that way.

Most of these codes are related to FOCUS files. If you are doing a CHECK FILE on a join structure, the linked segments would have no meaning.

I hope this helps you dicipher this a little better.

Thanks! Can you tell me then why this picture does not work and why the one below does?

DOES NOT WORK:
0 NUMBER OF ERRORS= 0
NUMBER OF SEGMENTS= 8 ( REAL= 2 VIRTUAL= 6 )
NUMBER OF FIELDS= 186 INDEXES= 0 FILES= 6
TOTAL LENGTH OF ALL FIELDS= 2394
1SECTION 01
STRUCTURE OF GNTINT FILE CSQASC_A ON 04/22/10 AT 16.59.22
CSQASC_>
01 S0
**************
*EMPLID **
*ACAD_CAREER **
*STDNT_CAR_N>**
*ADM_APPL_NBR**
* **
***************
**************
I
+-----------------+-----------------+-----------------+----------
I I I I
I ADM_APP> I CSQASC_> I CSQASC_> I CSQASC_>
02 I U 03 I KM 05 I KM 06 I KM
************** .............. .............. ..............
*OPRCLASS * :EMPLID ::K :EMPLID ::K :EMPLID ::K
*ADM_APPL_NBR* :ACAD_CAREER :: :PHONE_TYPE :: :TEST_ID ::
*EMPLID * :STDNT_CAR_N>:: :COUNTRY_CODE:: :TEST_COMPON>::
*INSTITUTION * :ADM_APPL_NBR:: :PHONE :: :TEST_DT ::
* * : :: : :: : ::
************** :............:: :............:: :............::
.............: .............: .............:
JOINEDI CSQASC_ADMJOINED CSQASC_SCCJOINED CSQASC_ST
I
I
I ADM_MAI>
04 I KLU
..............
:OPRCLASS :
:ADM_APPL_NBR:
:EMPLID :
:INSTITUTION :
: :
:............:
CSQASC_ADM_APPL_
1SECTION 02
-------+-----------------+
I I
I CSQASC_> I CSQASC_>
07 I KM 08 I KM
.............. ..............
:EMPLID ::K :EMPLID ::K
:ADDRESS_TYPE:: :NAME_TYPE ::
:EFF_STATUS :: :EFFDT ::
:ADDR_TYPE_D>:: :EFF_STATUS ::
: :: : ::
:............:: :............::
.............: .............:
JOINED CSQASC_PERJOINED CSQASC_SCC_NAMES



DOES WORK:
0 NUMBER OF ERRORS= 0
NUMBER OF SEGMENTS= 8 ( REAL= 2 VIRTUAL= 6 )
NUMBER OF FIELDS= 186 INDEXES= 0 FILES= 6
TOTAL LENGTH OF ALL FIELDS= 2394
1SECTION 01
STRUCTURE OF GNTINT FILE CSQASC_A ON 04/22/10 AT 17.01.59
CSQASC_>
01 S0
**************
*EMPLID **
*ACAD_CAREER **
*STDNT_CAR_N>**
*ADM_APPL_NBR**
* **
***************
**************
I
+-----------------+
I I
I ADM_APP> I CSQASC_>
02 I U 03 I KM
************** ..............
*OPRCLASS * :EMPLID ::K
*ADM_APPL_NBR* :ACAD_CAREER ::
*EMPLID * :STDNT_CAR_N>::
*INSTITUTION * :ADM_APPL_NBR::
* * : ::
************** :............::
.............:
JOINEDI CSQASC_ADM_APPL_
+-----------------+-----------------+-----------------+----------
I I I I
I ADM_MAI> I CSQASC_> I CSQASC_> I CSQASC_>
04 I KLU 05 I KM 06 I KM 07 I KM
.............. .............. .............. ..............
:OPRCLASS : :EMPLID ::K :EMPLID ::K :EMPLID ::K
:ADM_APPL_NBR: :PHONE_TYPE :: :TEST_ID :: :ADDRESS_TYPE::
:EMPLID : :COUNTRY_CODE:: :TEST_COMPON>:: :EFF_STATUS ::
:INSTITUTION : :PHONE :: :TEST_DT :: :ADDR_TYPE_D>::
: : : :: : :: : ::
:............: :............:: :............:: :............::
CSQASC_ADM.............: .............: .............:
JOINED CSQASC_SCCJOINED CSQASC_STDJOINED CSQASC_PE
1SECTION 02
-------+
I
I CSQASC_>
08 I KM
..............
:EMPLID ::K
:NAME_TYPE ::
:EFFDT ::
:EFF_STATUS ::
: ::
:............::
.............:
JOINED CSQASC_SCC_NAMES



Malinda

Can you put the structure in the code tags, use the button

I am not following, sorry. I am pretty new to all this stuff

malinda

Oops, I did the same thing, heres another try.

Use the

</>

button to put the

[CODE]  

[/CODE]
tags in the post, and paste the code/output between them.

This message has been edited. Last edited by: Waz,

Is that going to be right after my check file/picture commands?

malinda

when posting, it should look like

[CODE]

DOES NOT WORK:
0 NUMBER OF ERRORS= 0
NUMBER OF SEGMENTS= 8 ( REAL= 2 VIRTUAL= 6 )
NUMBER OF FIELDS= 186 INDEXES= 0 FILES= 6
TOTAL LENGTH OF ALL FIELDS= 2394
1SECTION 01
STRUCTURE OF GNTINT FILE CSQASC_A ON 04/22/10 AT 16.59.22
CSQASC_>
01 S0
**************
.
.
.

[/code]

 01      S0
 **************
 *EMPLID      **
 *ACAD_CAREER **
 *STDNT_CAR_N>**
 *ADM_APPL_NBR**
 *            **
 ***************
  **************
       I
       +-----------------+
       I                 I
       I ADM_APP>        I CSQASC_>
 02    I U         03    I KM
 **************    ..............
 *OPRCLASS    *    :EMPLID      ::K
 *ADM_APPL_NBR*    :ACAD_CAREER ::
 *EMPLID      *    :STDNT_CAR_N>::
 *INSTITUTION *    :ADM_APPL_NBR::
 *            *    :            ::
 **************    :............::
                    .............:
                   JOINEDI CSQASC_ADM_APPL_
                         +-----------------+-----------------+-----------------+-----------------+                
                         I                 I                 I                 I                 I                       
                         I ADM_MAI>        I CSQASC_>        I CSQASC_>        I CSQASC_>        I CSQASC_>              
                   04    I KLU       05    I KM        06    I KM        07    I KM        08    I KM              
                   ..............    ..............    ..............    ..............    ..............          
                   :OPRCLASS    :    :EMPLID      ::K  :EMPLID      ::K  :EMPLID      ::K  :EMPLID      ::K        
                   :ADM_APPL_NBR:    :PHONE_TYPE  ::   :TEST_ID     ::   :ADDRESS_TYPE::   :NAME_TYPE   ::         
                   :EMPLID      :    :COUNTRY_CODE::   :TEST_COMPON>::   :EFF_STATUS  ::   :EFFDT       ::         
                   :INSTITUTION :    :PHONE       ::   :TEST_DT     ::   :ADDR_TYPE_D>::   :EFF_STATUS  ::         
                   :            :    :            ::   :            ::   :            ::   :            ::         
                   :............:    :............::   :............::   :............::   :............::         
                           CSQASC_ADM .............:    .............:    .............:    .............:          
                                     JOINED CSQASC_SCC JOINED CSQASC_STD JOINED CSQASC_PE  JOINED CSQASC_SCC_NAMES

Short explanation of the used indicators:
01/02/03 etc are the segment numbers as focus sees them. The number is also the order in which focus will retrieve the data.
S0 - segment type S0 - unsorted data. Typical for RDBMS tables
U - Unique. Indicates a 1:1 relationship.
KM - Keyed Many - indicates a 1:n relationship.
KLU - Key linked unique - indicates a join with a 1:1 relationship
A box lined with *'s indicate a segment that is in the host master file.
A box lined with :'s and .'s indicate a joined structure.
A 'K' to the right of the field indicates that this field is a key-field.
==========================
You did not mention what it means that it does or does not work. Do you get error messages?

Hope this helps a bit.

That does help thanks GamP.

With the one that I say doesn't work I get the all verb objects must be in sam path as their sort fields error.

Malinda

Any suggestions/general rules when doing joins in order to avoid this?

Malinda

One simple approach is to eliminate the sorts and put all in a hold file which effectively flattens out the hierarchy. Then you can sort to your heart's content.

Very nice and very true. I may try this.....still curious though as to why some join structures are better than others and how to tell and avoid this so I don't use hold files as a crutch

malinda

You don't always have to use hold files but here is a write-up of the technique I mentioned:
http://techsupport.information...om/sps/23322550.html
Another one is to do a 1:1 join (omit the MULTIPLE or ALL keyword) where it makes sense. Then that segment is considered part of the parent and on the same path.

If you think about it, sorting on columns in sibling children doesn't always make logical sense and you could get incorrect results in your answer set.