FIND: Verifying the Existence of a Value in a Data Source

How to:

Available Languages: MODIFY, Maintain

The FIND function determines if a data value is in a data source field being searched. The function sets a temporary field to 1 (a non-zero value for MODIFY) if the data value is found in the data source field, and to 0 if it is not. FIND does not change the searched file's current database position. A value greater than zero confirms the presence of the data value, not the number of instances in the data source field.

Note: For MODIFY only, the FIND function verifies the existence of a incoming data value in an indexed FOCUS data source field.

You can also use FIND in a VALIDATE command to determine if a transaction field value exists in another FOCUS data source. If the field value is not in that data source, the function returns a value of 0, causing the validation test to fail and the request to reject the transaction.

You can use any number of FINDs in a COMPUTE or VALIDATE command. However, more FINDs increase processing time and require more buffer space in memory.

Limit: FIND does not work on files with different DBA passwords.

The opposite of FIND is NOT FIND. The NOT FIND function sets a temporary field to 1 if the incoming value is not in the data source and to 0 if the incoming value is in the data source.

Top of page
x
Syntax: How to Verify the Existence of a Value in a Data Source
FIND(fieldname [AS dbfield] IN file);

where:

fieldname

Is the name of the field that contains the incoming data value.

AS dbfield

Is the name of the data source field whose values are compared to the incoming field values.

For Maintain - the AS field is required and the name must be qualified.

For MODIFY - the AS field must be indexed. If the incoming field and the data source field have the same name, omit this phrase.

file

Is the name of the FOCUS data source.

For Maintain - the IN file is unnecessary since the AS field name is required and must be qualified.

For MODIFY - the IN field must be indexed.

Note:
Example: Verifying the Existence of a Value in Another Data Source (Maintain)

In the following example, FIND determines if a data value is found in another data source. 

MAINTAIN FILE MOVIES AND VIDEOTRK
FOR ALL NEXT MOVIES.MOVIECODE INTO FILMSTK
TYPE "RC SHOULD BE 1 WHERE MOVIECODE EXISTS IN BOTH FILES";
TYPE " "
COMPUTE RC/I1;
COMPUTE I/I1=1;
REPEAT FILMSTK.FOCCOUNT
  COMPUTE RC= FIND(FILMSTK(I).MOVIECODE AS VIDEOTRK.MOVIECODE)
  TYPE "FOR MOVIECODE = <<FILMSTK(I).MOVIECODE , RC = <<RC"
  COMPUTE I=I+1;
ENDREPEAT
END

The output is:

RC SHOULD BE 1 WHERE MOVIECODE EXISTS IN BOTH FILES
 FOR MOVIECODE = 001MCA, RC = 1
 .
 .
 .
 FOR MOVIECODE = 387PLA, RC = 0
 .
 .
 .
 FOR MOVIECODE = 963CBS, RC = 1
 TRANSACTIONS: COMMITS  =    1 ROLLBACKS =    0
 SEGMENTS    : INCLUDED =    0 UPDATED   =    0 DELETED   =    0
Example: Verifying the Existence of a Value in the Same Data Source (Maintain)

In the following example, FIND determines if a data value is found in the same data source. 

MAINTAIN FILE CAR
COMPUTE RETAIL_COST=31500;
COMPUTE CHECK/I1;
COMPUTE CHECK= FIND (RETAIL_COST);
   IF CHECK = 1 THEN GOTO FOUND1
      ELSE GOTO NOT1;
CASE FOUND1
TYPE "THERE IS A CAR WITH A RETAIL_COST OF <<RETAIL_COST"
-* ....
ENDCASE
CASE NOT1
TYPE "THERE IS NO CAR WITH A RETAIL_COST OF <<RETAIL_COST"
-*....
ENDCASE
-*....
END

The output is:

THERE IS A CAR WITH A RETAIL_COST OF    31,500
 TRANSACTIONS: COMMITS   =    1 ROLLBACKS =    0
 SEGMENTS    : INCLUDED  =    0 UPDATED   =    0 DELETED   =    0
Example: Verifying the Existence of a Value in an Indexed Field (MODIFY)

FIND determines if a supplied value in the EMP_ID field is in the EDUCFILE data source. The procedure then displays a message indicating the result of the search. 

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
COMPUTE
   EDTEST = FIND(EMP_ID IN EDUCFILE);
   MSG/A40 = IF EDTEST NE 0 THEN
      'STUDENT LISTED IN EDUCATION FILE' ELSE
      'STUDENT NOT LISTED IN EDUCATION FILE';
MATCH EMP_ID
      ON NOMATCH TYPE "<MSG"
      ON MATCH TYPE "<MSG"
DATA

A sample execution is:

>
 EMPLOYEE ON 12/04/2001 AT 12.09.03
 DATA FOR TRANSACTION                  1
 
 EMP_ID               =
112847612
 STUDENT LISTED IN EDUCATION FILE
 DATA FOR TRANSACTION                  2
 
 EMP_ID               =
219984371
 STUDENT NOT LISTED IN EDUCATION FILE
 DATA FOR TRANSACTION                  3

The procedure processes as follows:

  1. The procedure prompts you for an employee ID. You enter 112847612.
  2. The procedure searches the EDUCFILE data source for the employee ID 112847612. It finds the ID so it prints STUDENT LISTED IN EDUCATION FILE.
  3. The procedure prompts you for an employee ID. You enter 219984371.
  4. The procedure searches the EDUCFILE data source for the employee ID 219984371. It does not find the ID so it prints STUDENT NOT LISTED IN EDUCATION FILE.
Example: Rejecting a Transaction When a Value Is Not Found (MODIFY)

The following updates the number of hours an employee spent in class. The VALIDATE command rejects a transaction for an employee whose ID is not found in the EDUCFILE data source, which records class attendance. 

MODIFY FILE EMPLOYEE
PROMPT EMP_ID ED_HRS
VALIDATE
   EDTEST = FIND(EMP_ID IN EDUCFILE);
MATCH EMP_ID
   ON NOMATCH REJECT
   ON MATCH UPDATE ED_HRS
DATA

A sample execution is:

>
 EMPLOYEE ON 12/04/2001 AT 12/26/08
 DATA FOR TRANSACTION        1
 
 EMP_ID       =
112847612
 ED_HRS     =
7
 DATA FOR TRANSACTION        2
 
 EMP_ID      =
219984371
 ED_HRS     =
0
 (FOC421) TRANS 2 REJECTED INVALID EDTEST
 219984371, 0, $
 DATA FOR TRANSACTION        3

The procedure processes as follows:

  1. The procedure prompts you for an employee ID and the number of hours the employee spent in class. You enter the following data:
    EMP_ID:  112847612
ED_HRS:  7
  2. The procedure updates the number of hours for the ID 112847612.
  3. The procedure prompts you for an employee ID and the number of hours the employee spent in class. You enter the following data:
    EMP_ID:  219984371
ED_HRS:  0
  4. The procedure rejects the record for the ID 219984371 because it does not exist in the EDUCFILE data source, and an error message is returned.
Information Builders