Conference ilbbak::ibi_focus

399.0. "DEFINE FILE problems" by DUCK::MCSHANEG (Meashley Blagwart Esq.) Wed Feb 27 1991 10:16

  
  
  FOCUS provides a facility which allows you to list search criteria in an 
  external file which can then be FILEDEF'd for access.
  
  e.g. 
  
  FILEDEF SEARCH DISK SYS$LOGIN:SEARCH.DAT
  TABLE FILE DATABASE
  SUM value_field
  IF fieldname IS (SEARCH)
  END
  
  This works fine. However if you try to use the same 'lookup' facility with 
  DEFINE FILE you discover that in order to make the statement acceptable to 
  FOCUS the 'lookup' table must be enclosed in single quotes else FOCUS fails 
  with syntax errors. 
  
  this fails:-
  
  DEFINE FILE DATABASE
  VAL = IF field1 IS (SEARCH) THEN amount_field ELSE 0;
  END
  TABLE FILE DATABASE
  SUM VAL
  END
  
  The following works, syntacticaly, but produces no data. When you refer to 
  the defined field within the table request no values are found.
  
  DEFINE FILE DATABASE
  VAL = IF field1 IS '(SEARCH)' THEN amount_field ELSE 0;
  END
  TABLE FILE DATABASE
  SUM VAL
  END
  
  
  Has anybody come across this problem ? Any suggestions for a 'fix' or an 
  alternative would be appreciated.
  
  Gary.
  
    

    
    As long as your search file contains just a list of valid values for
    field1 then you can use a DECODE statement in your DEFINE expression:
    
    DEFINE FILE DATABASE
    VAL = IF DECODE field1( SEARCH ) THEN amount_field ELSE 0;
    END
    TABLE FILE DATABASE
    SUM VAL
    END
    
    Hope this helps,
    David

    
    Try this,
    
    FILEDEF SEARCH DISK SEARCH.DAT
    
    DEFINE FILE YOURFILE
      VAL_ALPHA/A10 = DECODE AMT_FIELD(SEARCH ELSE 'Z');
      VAL_AMT/P10   = EDIT(VAL_ALPHA);
    END
    
    TABLE FILE YOURFILE
    SUM VAL_AMT 
    
    IF VAL_ALPHA NE 'Z'
    
    END