Retrieving references to a record
In the standard SQL interface, you can retrieve references to a record as follows:
| Suppose that tiitm001 has a reference to tccom010 (field 'cuno'),
| and to tccom011 (field 'suno')
table ttiitm001
table ttccom010
table ttccom011
SELECT tiitm001.*, tccom010.*, tccom011.*
FROM tiitm001, tccom010, tccom011
WHERE tiitm001.cuno = tccom010.cuno AND
tiitm001.suno = tccom011.suno

Retrieving references using REFERS TO
To simplify the retrieval of references, you can specify references (using the keyword REFERS TO) in the WHERE clause. Apart from simplifying the query, this also optimizes query handling. As references always refer to a primary key, they can be found immediately. Moreover, the program fills a field with reference characters if it does not find a reference. The following implements the previous example by using REFERS TO:
table ttiitm001
table ttccom010
table ttccom011
SELECT tiitm001.*, tccom010.*, tccom011.*
WHERE tiitm001.cuno REFERS TO tccom010 AND
tiitm001.suno REFERS TO tccom011
| OR
SELECT tiitm001.*, tccom010.*, tccom011.*
WHERE tiitm001 REFERS TO tccom010 AND
tiitm001 REFERS TO tccom011

REFERS TO syntax
A REFERS TO statement has the following form:
<from> REFERS TO <to> [PATH <path> [,<path>...]] [UNREF<mode>]
The following table explains the various parts of the statement:
<from> The referring table field or table.
<to> The table referred to.
<path> The path via which reference is reached (always table fields). If PATH is specified, specifying a table field for <from> is mandatory. For example:WHERE table1.field REFERS TO table4.fieldPATH table2.field, table3.field
<mode> A mode indicating system action if reference does not exist; possible values are:SKIP If a reference cannot be found, the record is skipped.CLEAR If a 
                        reference is empty or absent, the referring 
record is filled with spaces or 0 (numeric).SETUNREF The value of an undefined 
                        reference is filled with 
an 'undefined reference' sign, defined in the data 
dictionary, or with 0 (numeric).CLEARUNREF The referred record is filled with spaces or 0 
(zero) when reference fields are empty. When 
the reference is undefined the referred record 
is filled with an 'undefined reference' sign.Depending on the reference       
                                definition in the data dictionary, the default reference mode is:
reference mode in DD                         UNREF mode
mandatory                                         SETUNREF
mandatory unless empty                CLEARUNREF
not mandatory                                 CLEAR

Related Post:

People who read this post also read :


Post a Comment


Twitter Delicious Facebook Digg Stumbleupon Favorites More