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
Related Post:
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:
0 comments:
Post a Comment