DB_INFILE: Testing Values Against a File or an SQL Subquery

How to:

Reference:

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:


Top of page

x
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).


Top of page

x
Reference: Usage Notes for DB_INFILE


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, $

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

TABLE FILE WF_RETAIL
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  


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.

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:


Top of page

x
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.


iWay Software