How to: |
The FOCUS join command and conditional join command have a FULL OUTER join option.
A full outer join returns all rows from the source data source and all rows from the target data source. Where values do not exist for the rows in either data source, null values are returned. FOCUS substitutes default values on the report output (blanks for alphanumeric columns, the NODATA symbol for numeric columns).
The full outer join is only supported for use with those relational data sources that support this type of join, in which case the FOCUS join syntax is optimized (translated to the full outer join SQL syntax supported by the RDBMS). Use of this syntax for any data source that does not support a full outer join produces an error message.
The following syntax generates a full outer equijoin based on real fields:
JOIN FULL_OUTER hfld1 [AND hfld2 ...] IN table1 [TAG tag1] TO {UNIQUE|MULTIPLE} cfld [AND cfld2 ...] IN table2 [TAG tag2] [AS joinname] END
where:
Is the name of a field in the host table containing values shared with a field in the cross-referenced table. This field is called the host field.
Can be an additional field in the host table. The phrase beginning with AND is required when specifying multiple fields.
Is the name of the host table.
Is a tag name of up to 66 characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in the host table.
The tag name for the host table must be the same in all the JOIN commands of a joined structure.
Is the name of a field in the cross-referenced table containing values that match those of hfld1 (or of concatenated host fields). This field is called the cross-referenced field.
Note: UNIQUE returns only one instance and, if there is no matching instance in the cross-referenced table, it returns null values.
Use the MULTIPLE parameter when crfld1 may have multiple instances in common with one value in hfld1. Note that ALL is a synonym for MULTIPLE, and omitting this parameter entirely is a synonym for UNIQUE.
Is the name of a field in the cross-referenced table with values in common with hfld2.
Note: crfld2 may be qualified. This field is only available for adapters that support multi-field joins.
Is the name of the cross-referenced table.
Is a tag name of up to 66 characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in cross-referenced tables. In a recursive join structure, if no tag name is provided, all field names and aliases are prefixed with the first four characters of the join name.
The tag name for the host table must be the same in all the JOIN commands of a joined structure.
Is an optional name of up to eight characters that you may assign to the join structure. You must assign a unique name to a join structure if:
Note: If you do not assign a name to the join structure with the AS phrase, the name is assumed to be blank. A join without a name overwrites an existing join without a name.
Required when the JOIN command is longer than one line. It terminates the command and must be on a line by itself.
The following syntax generates a DEFINE-based full outer join:
JOIN FULL_OUTER deffld WITH host_field ... IN table1 [TAG tag1] TO [UNIQUE|MULTIPLE] cr_field IN table2 [TAG tag2] [AS joinname] END
where:
Is the name of a virtual field for the host file (the host field). The virtual field can be defined in the Master File or with a DEFINE command.
Is the name of any real field in the host segment with which you want to associate the virtual field. This association is required to locate the virtual field.
The WITH phrase is required unless the KEEPDEFINES parameter is set to ON and deffld was defined prior to issuing the JOIN command.
To determine which segment contains the virtual field, use the ? DEFINE query after issuing the DEFINE command.
Is the name of the host table.
Is a tag name of up to 66 characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in host tables.
The tag name for the host table must be the same in all JOIN commands of a joined structure.
Is the name of a real field in the cross-referenced table whose values match those of the virtual field. This must be a real field declared in the Master File.
Note: UNIQUE returns only one instance and, if there is no matching instance in the cross-referenced table, it returns null values.
Use the MULTIPLE parameter when crfld1 may have multiple instances in common with one value in hfld1. Note that ALL is a synonym for MULTIPLE, and omitting this parameter entirely is a synonym for UNIQUE.
Is the name of the cross-referenced table.
Is a tag name of up to 66 characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in cross-referenced tables. In a recursive joined structure, if no tag name is provided, all field names and aliases are prefixed with the first four characters of the join name.
The tag name for the host file must be the same in all JOIN commands of a joined structure.
Is an optional name of up to eight characters that you may assign to the joined structure. You must assign a unique name to a join structure if:
If you do not assign a name to the joined structure with the AS phrase, the name is assumed to be blank. A join without a name overwrites an existing join without a name.
Required when the JOIN command is longer than one line. It terminates the command and must be on a line by itself.
The following syntax generates a full outer conditional join:
JOIN FULL_OUTER FILE table1 AT hfld1 [WITH hfld2] [TAG tag1] TO {UNIQUE|MULTIPLE} FILE table2 AT crfld [TAG tag2] [AS joinname] [WHERE expression1; [WHERE expression2; ...] END
where:
Is the host Master File.
Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.
Is the field name in the host Master File whose segment will be joined to the cross-referenced table. The field name must be at the lowest level segment in its data source that is referenced.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the host table.
Is a table column with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.
Specifies a one-to-many relationship between table1 and table2. Note that ALL is a synonym for MULTIPLE.
Specifies a one-to-one relationship between table1 and table2. Note that ONE is a synonym for UNIQUE.
Note: The join to UNIQUE will return only one instance of the cross-referenced table, and if this instance does not match based on the evaluation of the WHERE expression, null values are returned.
Is the cross-referenced Master File.
Is the join field name in the cross-referenced Master File. It can be any field in the segment.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced table.
Is the name associated with the joined structure.
Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.
The END command is required to terminate the command and must be on a line by itself.
The following requests generate two tables to join, and then issues a request against the join. The tables are generated using the WFLITE sample.
The following request generates the WF_SALES table. The field ID_PRODUCT will be used in the full outer join command. The generated table will contain ID_PRODUCT values from 2150 to 4000:
TABLE FILE WFLITE SUM GROSS_PROFIT_US PRODUCT_CATEGORY PRODUCT_SUBCATEG BY ID_PRODUCT WHERE ID_PRODUCT FROM 2150 TO 4000 ON TABLE HOLD AS WF_SALES FORMAT DB2 END
The following request generates the WF_PROD table. The field ID_PRODUCT will be used in the full outer join command. The generated table will contain ID_PRODUCT values from 3000 to 5000:
TABLE FILE WFLITE SUM PRICE_DOLLARS PRODUCT_CATEGORY PRODUCT_SUBCATEG PRODUCT_NAME BY ID_PRODUCT WHERE ID_PRODUCT FROM 3000 TO 5000 ON TABLE HOLD AS WF_PROD FORMAT DB2 END
The following request issues the JOIN command and displays values from the joined tables:
SET TRACEUSER=ON SET TRACESTAMP=OFF SET TRACEOFF=ALL SET TRACEON = STMTRACE//CLIENT JOIN FULL_OUTER ID_PRODUCT IN WF_PROD TAG T1 TO ALL ID_PRODUCT IN WF_SALES TAG T2 END TABLE FILE WF_PROD PRINT T1.ID_PRODUCT AS 'Product ID' PRICE_DOLLARS AS Price T2.ID_PRODUCT AS 'Sales ID' GROSS_PROFIT_US BY T1.ID_PRODUCT NOPRINT ON TABLE SET PAGE NOPAGE END
The trace shows that the full outer join was optimized (translated to SQL) so that DB2 could process the join:
SELECT T1."ID_PRODUCT", T1."PRICE_DOLLARS", T2."E01", T2."E021" FROM ( WF_PROD T1 FULL OUTER JOIN WF_SALES T2 ON T2."E01" = T1."ID_PRODUCT" ) ORDER BY T1."ID_PRODUCT" FOR FETCH ONLY;
The output has a row for each ID_PRODUCT value that is in either table.
Product ID Price Sales ID Gross Profit ---------- ----- -------- ------------ . . 2158 $11,267.75 . . 2157 $6,223.34 . . 2156 $14,484.75 . . 2155 $12,472.93 . . 2154 $19,913.65 . . 2153 $7,291.10 . . 2152 $6,766.45 . . 2151 $7,172.24 . . 2167 $6,771.26 . . 2166 $12,469.45 . . 2165 $8,540.53 . . 2164 $5,615.33 . . 2163 $21,347.98 . . 2162 $9,197.81 . . 2161 $8,683.00 . . 2160 $7,194.15 . . 2159 $9,015.75 3001 47,988.00 3001 $12,786.30 3002 34,990.00 3002 $5,586.10 3003 30,591.00 3003 $12,399.15 3004 44,187.00 3004 $8,434.95 3005 30,057.00 3005 $8,987.80 3006 25,348.05 3006 $6,422.59 3007 23,607.00 3007 $8,354.60 3008 42,898.57 3008 $18,469.01 3009 48,139.00 3009 $22,542.75 3010 44,850.00 3010 $16,722.15 3011 28,078.44 3011 $18,204.82 3012 31,146.00 3012 $19,739.45 3013 27,208.00 3013 $17,983.00 3014 23,968.59 3014 $19,996.09 3015 29,061.45 3015 $17,420.72 4012 43,998.35 . . 4013 24,749.89 . . 4014 33,966.00 . . 4015 20,998.95 . . 4016 32,899.06 . . 4017 26,399.67 . . 4018 39,920.00 . . 5000 23,799.15 . .
Rows with ID_PRODUCT values from 2150 to 2167 are only in the WF_SALES table, so the columns from WF_PROD display the NODATA symbol. Rows with ID_PRODUCT values above 4000 are only in the WF_PROD table, so the columns from WF_SALES display the NODATA symbol. Rows with ID_PRODUCT values from 3000 to 4000 are in both tables, so all columns have values.
Information Builders |