DB_INFILE: Testing Values Against a File or an SQL Subquery

How to:

Compare Source and Target Field Values With DB_INFILE
Control DB_INFILE Optimization

Reference:

Usage Notes for DB_INFILE

The DB_INFILE function compares one or more field values in a source file to values in a target file. The comparison can be based on one or more field values. DB_INFILE returns the value 1 (TRUE) if the set of source fields matches a set of values from the target file. Otherwise, the function returns 0 (zero, FALSE). DB_INFILE can be used where a function is valid in a FOCUS request, such as in a DEFINE or a WHERE phrase.

The target file can be any data source that FOCUS can read. Depending on the data sources accessed and the components in the request, either FOCUS or an RDBMS will process the comparison of values.

If FOCUS processes the comparison, it reads the target data source and dynamically creates a sequential file containing the target data values, along with a synonym describing the data file. It then builds IF or WHERE structures in memory with all combinations of source and target values. If the target data contains characters that FOCUS considers wildcard characters, it will treat them as wildcard characters unless the command SET EQTEST = EXACT is in effect.

The following situations exist when a relational data source is the source file:

The target values are in a relational data source from the same RDBMS and connection. In this case, the target file referenced by DB_INFILE can be:
- An SQL file containing a subquery that retrieves the target values. A synonym must exist that describes the target SQL file. The Access File must specify the CONNECTION and DATASET for the target file.
  If the subquery results in a SELECT statement supported by the RDBMS, the relational adapter inserts the subquery into the WHERE predicate of the generated SQL.
  
  If the subquery does not result in a valid SELECT statement for the RDBMS, the relational adapter retrieves the target values. It then generates a WHERE predicate, with a list of all combinations of source and target field values.
  
  You can create an SQL file containing a subquery and a corresponding synonym using the HOLD FORMAT SQL_SCRIPT command. For more information, see theCreating Reportsmanual.
- A relational data source. A synonym must exist that describes the target data source.
  If the data source contains only those fields referenced by DB_INFILE as target fields, the relational adapter creates a subquery that retrieves the target values. If the subquery results in a SELECT statement supported by the RDBMS, the relational adapter inserts the subquery into the WHERE predicate of the generated SQL.
  
  If the subquery does not result in a valid SELECT statement for the RDBMS, the relational adapter retrieves a unique list of the target values. It then generates a WHERE predicate with a list of all combinations of source and target field values.
The target values are in a non-relational data source or a relational data source from a different RDBMS or connection. In this case, the target values are retrieved and passed to FOCUS for processing.

Syntax: How to Compare Source and Target Field Values With DB_INFILE

DB_INFILE(target_file, s1, t1, ... sn, tn)

where:

target_file: Is the synonym for the target file.
s1, ..., sn: Are fields from the source file.
t1, ..., tn: Are fields from the target file.

The function returns the value 1 if a set of target values matches the set of source values. Otherwise, the function returns a zero (0).

Reference: Usage Notes for DB_INFILE

If both the source and target data sources have MISSING=ON for a comparison field, then a missing value in both files is considered an equality. If MISSING=OFF in one or both files, a missing value in one or both files results in an inequality.
Values are not padded or truncated when compared, except when comparing date and date-time values.
- If the source field is a date field and the target field is a date-time field, the time component is removed before comparison.
- If the source field is a date-time field and the target field is a date field, a zero time component is added to the target value before comparison.
If an alphanumeric field is compared to a numeric field, an attempt will be made to convert the alphanumeric value to a number before comparison.
If FOCUS processes the comparison, and the target data contains characters that FOCUS considers wildcard characters, it will treat them as wildcard characters unless the command SET EQTEST = EXACT is in effect.

Example: Comparing Source and Target Values Using an SQL Subquery File

This example uses the WF_RETAIL DB2 data source.

The SQL file named retail_subquery.sql contains the following subquery that retrieves specified state codes in the Central and NorthEast regions:

SELECT  MAX(T11.REGION), MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', 'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))  GROUP BY T11.REGION, T11.STATECODE

The retail_subquery.mas Master File follows:

FILENAME=RETAIL_SUBQUERY, SUFFIX=DB2     , $
  SEGMENT=RETAIL_SUBQUERY, SEGTYPE=S0, $
    FIELDNAME=REGION, ALIAS=E01, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON, $
    FIELDNAME=STATECODE, ALIAS=E02, USAGE=A2, ACTUAL=A2,
      MISSING=ON, $

The retail_subquery.acx Access File follows:

SEGNAME=RETAIL_SUBQUERY, CONNECTION=CON1, DATASET=RETAIL_SUBQUERY.SQL, $

Note: You can create an SQL subquery file, along with a corresponding synonym, using the HOLD FORMAT SQL_SCRIPT command. For more information, see the Creating Reports manual.

The following request uses the DB_INFILE function to compare region names and state codes against the names retrieved by the subquery:

TABLE FILE WFLITE
SUM REVENUE
BY REGION
BY STATECODE
WHERE DB_INFILE(RETAIL_SUBQUERY, REGION, REGION, STATECODE, STATECODE)
ON TABLE SET PAGE NOPAGE
END

The trace shows that the subquery was inserted into the WHERE predicate in the generated SQL:

 SELECT   
  T11."REGION",  
  T11."STATECODE",  
   SUM(T1."Revenue")  
   FROM   
  wrd_fact_sales T1,  
  wrd_dim_customer T5,  
  wrd_dim_geography T11  
   WHERE   
  (T5."ID_CUSTOMER" = T1."ID_CUSTOMER") AND   
  (T11."ID_GEOGRAPHY" = T5."ID_GEOGRAPHY") AND  
  ((T11."REGION", T11."STATECODE") IN (SELECT  MAX(T11.REGION),  
  MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE  
  (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA',  
  'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))   
  GROUP BY T11.REGION, T11.STATECODE))  
   GROUP BY   
  T11."REGION",  
  T11."STATECODE  "
   ORDER BY   
  T11."REGION",  
  T11."STATECODE  "
   FOR FETCH ONLY; 
END

The output is:

Example: Comparing Source and Target Values Using a Sequential File

The empvalues.ftm sequential file contains the last and first names of employees in the MIS department:

SMITH           MARY        JONES           DIANE       MCCOY           JOHN        BLACKWOOD       ROSEMARIE   GREENSPAN       MARY        CROSS           BARBARA

The empvalues.mas Master File describes the data in the empvalues.ftm file

FILENAME=EMPVALUES, SUFFIX=FIX     , IOTYPE=BINARY, $
  SEGMENT=EMPVALUE, SEGTYPE=S0, $
    FIELDNAME=LN, ALIAS=E01, USAGE=A15, ACTUAL=A16, $
    FIELDNAME=FN, ALIAS=E02, USAGE=A10, ACTUAL=A12, $

Note: You can create a sequential file, along with a corresponding synonym, using the HOLD FORMAT SQL_SCRIPT command. For more information, see the Creating Reports manual.

The following request against the FOCUS EMPLOYEE data source uses the DB_INFILE function to compare employee names against the names stored in the empvalues.ftm file:

FILEDEF EMPVALUES DISK baseapp/empvalues.ftm
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY LAST_NAME BY FIRST_NAME
WHERE DB_INFILE(EMPVALUES, LAST_NAME, LN, FIRST_NAME, FN)
ON TABLE SET PAGE NOPAGE
END

The output is:

Syntax: How to Control DB_INFILE Optimization

To control whether to prevent optimization of the DB_INFILE expression, issue the following command:

SET DB_INFILE = {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

In a TABLE request, issue the following command:

ON TABLE SET DB_INFILE  {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

where:

DEFAULT: Enables DB_INFILE to create a subquery if its analysis determines that it is possible. This is the default value.
EXPAND_ALWAYS: Prevents DB_INFILE from creating a subquery. Instead, it expands the expression into IF and WHERE clauses in memory.
EXPAND_NEVER: Prevents DB_INFILE from expanding the expression into IF and WHERE clauses in memory. Instead, it attempts to create a subquery. If this is not possible, a FOC32585 message is generated and processing halts.