I have a FOCUS flat file that contains multiple lists of masks that can be used to extract fields from another file.
How can I extract the information correctly if the masks in the Mask File are similar to this:
Mask1 Mask2 Mask3
66$$$$ 13$$$$ (blank)
66$$$$ 03$$$$ (blank)
66$$$$ (blank) C
Addresses of Parts extracted from the main file must start with
66 and 13
or 66 and 03
or 66 and (anything) and 'C'

Users can change the masks in the Mask File at anytime, including adding/changing the mask from four $ in the A6 field to one or three $ in the A6 field.

The masks can be of various lengths based on the number of $ in the mask but all A6.

I have had limited success using:
WHERE fieldname1 EQ (holdfilename1)
WHERE fieldname2 EQ (holdfilename2)
or
WHERE fieldname1 IN FILE holdfilename1
WHERE fieldname2 IN FILE holdfilename2

But it keeps ANDing the results, I need to OR the results which this type of WHERE statement cannot do in a compound way, i.e. I need this to happen but get an error:
WHERE (fieldname1 EQ (holdfilename1)) OR (fieldname2 EQ (holdfilename2))

or

WHERE (fieldname1 IN FILE holdfilename1) OR (fieldname2 IN FILE holdfilename2)

This message has been edited. Last edited by: Kerry,

If I understand your requirements correctly, you can't do WHERE... IN FILE...

But the WHERE ... OR ... should work.

What error do you get?

This message has been edited. Last edited by: Francis Mariani,

I can do this WHERE function:
WHERE fieldname1 EQ (holdfilename1)
WHERE fieldname2 EQ (holdfilename2)

and I can do this WHERE function:
WHERE fieldname1 IN FILE holdfilename1
WHERE fieldname2 IN FILE holdfilename2
But that is the equivalent of ANDing the two WHERE statements together.
What I need to do would be the equivalent of ORing the two WHERE statements. Like this:
WHERE (fieldname1 EQ (holdfilename1)) OR (fieldname2 EQ (holdfilename2))

or using the WHERE function like this:
WHERE (fieldname1 IN FILE holdfilename1) OR (fieldname2 IN FILE holdfilename2)

But I get this error:
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: holdfilename1

Although I may have got the logic behind the masks and fields, you could try this concept.

EX -LINES 6 EDAPUT MASTER,msk_dat,CV,FILE
FILENAME=MSK_DAT, SUFFIX=FIX,$
SEGNAME=MSK_DAT, $
  FIELD=MASK1 ,ALIAS=  ,A7 ,A8 ,$
  FIELD=MASK2 ,ALIAS=  ,A7 ,A8 ,$
  FIELD=MASK3 ,ALIAS=  ,A7 ,A8 ,$


EX -LINES 4 EDAPUT FOCTEMP,msk_dat,CV,FILE
EN%     JA%     
EN%     JE%     
EN%             TR7

-* or you could use
-*EN_____ JA____  
-*EN_____ JE____  
-*EN_____         TR7

-RUN

FILEDEF MSK_DAT DISK msk_dat.ftm (LRECL 24

DEFINE  FILE MSK_DAT
 CNTR/I1 WITH MASK1 = LAST CNTR + 1 ;
 ACNT/A1  = EDIT(CNTR) ;
 MSK1/A26 = IF MASK1 NE ' ' THEN 'FieldName1 LIKE ''' || MASK1 || ''''  ELSE ' ' ;
 CND1/A3  = IF MSK1 NE ' ' THEN 'AND' ELSE ' ' ;
 MSK2/A56 = IF MASK2 EQ ' ' THEN MSK1 ELSE MSK1 | CND1 | ' FieldName2 LIKE ''' || MASK2 || '''' ;
 CND2/A3  = IF MSK2 NE ' ' THEN 'AND' ELSE ' ' ;
 MSK3/A86 = IF MASK3 EQ ' ' THEN MSK2 ELSE MSK2 | CND2 | ' FieldName3 LIKE ''' || MASK3 || '''' ;

 WHERE_TEXT/A6   = IF CNTR EQ 1 THEN 'WHERE' ELSE 'OR' ;
 WHERE_LINE/A100 = WHERE_TEXT | MSK3 ;
END

TABLE   FILE MSK_DAT
 PRINT  
        WHERE_LINE
 ON     TABLE SAVE AS WHR_COND
END

-RUN

DEFINE FILE CAR
 FieldName1/A10 = COUNTRY ;
 FieldName2/A16 = CAR ;
 FieldName3/A24 = MODEL ;
END

TABLE FILE CAR
PRINT FieldName1
      FieldName2
      FieldName3
-INCLUDE WHR_COND
END

Just got to make sure that the expression is correctly compounded

WHERE ((field IN FILE file1) OR (field IN FILE file2))

The following works no problem

FILEDEF F1 DISK f1.ftm
FILEDEF F2 DISK f2.ftm
FILEDEF F3 DISK f3.ftm
-RUN
-*
-WRITE F1 EN$*
-WRITE F2 F$*
-WRITE F3 JA$*
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE ((COUNTRY IN FILE F1) OR (COUNTRY IN FILE F2));
END
-RUN

Get the braces right and the worlds the limit

TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE (((COUNTRY IN FILE F1) OR (COUNTRY IN FILE F2)) AND (NOT CAR IN FILE F3));
END
-RUN

One can also use the old form (fewer keystrokes...):

  
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE ((COUNTRY EQ (F1)) OR (COUNTRY EQ (F2)));
END
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE (((COUNTRY EQ (F1)) OR (COUNTRY EQ (F2))) AND (CAR NE (F3)));
END

You need to test whether the three keys in a data record match the three respective masks in one of the rows of the user-maintained criterion file.

A series of file-tests, even connected with AND, won't work: It would select a data row if its three fields each matched some value in the three respective lists of mask values, even if the matches were not all from a single row in the original mask file.

Instead, construct the selection test as the comparison of a single field to a single file:

Define a single field to test, combining the three original data fields. Assuming they are each the same length, 6 characters, as the mask values in the illustration, just catenate them:
TESTFIELD/A18= FLD1 | FLD2 | FLD3 ;

Generate a flat file containing the user-supplied masks in corresponding form -- padded to the designated length with $'s, and catenated so they are column-aligned.

The code below illustrates the idea.

For clarity, I threw in a blank to separate the three fields and the three masks; and used LIST with IF TOTAL to identify source of the rows in the result.

I have not shown how to transform the user-supplied rows of mask values into the required form. You'll have to work that out -- either in Dialog Manager with a repeat loop, or with Define and Table operating (with a appropriate MFD) on the original file of mask values.

FILEDEF MASKS DISK MASKS.TXT
FILEDEF TESTDATA DISK TESTDATA.TXT
FILEDEF MFD      DISK TESTDATA.MAS
-RUN
-WRITE MASKS 55$$$$ 03$$$$ $$$$$$
-WRITE MASKS 66$$$$ 13$$$$ $$$$$$
-WRITE MASKS 77$$$$ $$$$$$ C$$$$$

-WRITE TESTDATA 55XXXX13YYYYCZZZZZ
-WRITE TESTDATA 55XXXX03YYYYZZZZZZ
-WRITE TESTDATA 66XXXX13YYYYZZZZZZ
-WRITE TESTDATA 77XXXXYYYYYYCZZZZZ
-WRITE TESTDATA ABCDEFGHIJKLMNOPQR

-WRITE MFD FILENAME=TESTDATA,SUFFIX=FIX
-WRITE MFD  SEGNAME=TESTDATA,SEGTYPE=S0
-WRITE MFD    FIELD=DATA1,DATA1,A6,$
-WRITE MFD    FIELD=DATA2,DATA2,A6,$
-WRITE MFD    FIELD=DATA3,DATA3,A6,$
-RUN
DEFINE FILE TESTDATA
TESTDATA/A20=DATA1 |' '| DATA2 |' '| DATA3;
END
TABLE FILE TESTDATA
LIST TESTDATA 
IF TOTAL TESTDATA EQ (MASKS)
END

This message has been edited. Last edited by: j.gross,

Jack,
You were correct on the ANDing. Your method of concatenating the fields worked.
Ready for part 2:
Now that I have the correct data filtered by the TESTFIELD, in the MASK file there is a Modifier field called MOD. Each of the newly filtered results records needs to be updated based on the MODifier of the set of Masks.
For "regular" data I would use a JOIN to join the two files together, allowing the "connection" of the new MODifier and update the old modifier, called LDTMMOD.
But, you cannot join a mask to real data. How do I pass the new MODifier to the new filtered results without a JOIN???

Oh ho -- so you need to know WHICH row of the mask-collection is matched. That's quite different from "IS THERE a row that matches".

The CHKFMT() function can be used, with the same 'combined mask values', to check each data record's key against one row of the mask collection.

Unfortunately, the mask argument of CHKFMT cannot be coded as the name of a variable -- you need to generate code that explicitly gives each mask as a quoted literal.

Using either DM or Define, generate a nested-IF test to look for the first matching mask-set and assign the corresponding Mod value -- something like the following (the lines between the comments are what needs to be generated dynamically, one line per row in the mask collection):

DEFINE FILE records
  COMBINED/A18=FLD1 | FLD2 | FLD3;
  MODVALUE/A20=
-* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       IF CHKFMT(18, COMBINED, '66$$$$03$$$$$$$$$$','I3') EQ 0 THEN 'AAAAAAAAAAAAAAAAAAAA'
  ELSE IF CHKFMT(18, COMBINED, '66$$$$13$$$$$$$$$$','I3') EQ 0 THEN 'BBBBBBBBBBBBBBBBBBBB'
  ELSE IF CHKFMT(18, COMBINED, '66$$$$$$$$$$C$$$$$','I3') EQ 0 THEN 'CCCCCCCCCCCCCCCCCCCC'
-* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  ELSE ' ';
END
TABLE FILE records
  PRINT FLD1 FLD2 FLD3 etc MODVALUE
  WHERE MODVALUE NE ' ';
  ON TABLE HOLD
END

The HOLD file can then be fed to a Modify or Maintain, to update the data source.

Jack,
Thank you. I was able to use your code with the concatenated Mask field:
AND COMPUTE TEST1/A13 = SA1 | SA2 | NEWABC;
against the inventory file's new concatenated field:
TEST2/A13 = TMPSA1 | TMPSA2 | TMPDUCATCD;
Along with code from Walter @ IBI:
ON TABLE HOLD AS HMOD FORMAT ALPHA
-READ HMOD NOCLOSE &TEST1.A13. &MODP.A3. &MOD.A3. &REST.A13.
-IF &TEST1 EQ ' ' GOTO SKIPX;
WHERE TEST2 EQ '&TEST1'
-SKIPX
ON TABLE HOLD AS TRANOUT FORMAT ALPHA
END
-RUN
-READREC
-READ HMOD NOCLOSE &TEST1.A13. &MODP.A3. &MOD.A3. &REST.A13.
-IF &IORETURN NE 0 GOTO RDDONE;
DYNAM COPY TRANOUTX TRANOUT APPEND
-RUN
-GOTO READREC
-RDDONE
TABLE FILE TRANOUT
PRINT *
END

And FINALLY received the results I needed for this MASKing MODifier project.
They need to upgrade you from Master to ...

Thanks again.
-robert-

I prefer a solution based solely on Table processing, to one that processes the lines of a file via a -READ loop.

The code below uses the Movies file (in apps/ibisamp). I used Category, Studio (part of Moviecode) and Rating as the three criterion columns. &MASK1 thru -3 represent the user-maintained control data (the three criteria plus an Action column). The code produces a PDF report listing the matching movies and the assigned action (the Mask data appear in the page footing).

-**** setup:
-SET &MASK1='CL,   ,G  ,BUY ,$';
-SET &MASK2='SC,   ,R  ,HOLD,$';
-SET &MASK3='MU,CBS,   ,SELL,$';
FILEDEF MASKS DISK MASKS.TXT
FILEDEF MFD   DISK MASKS.MAS
-RUN
-WRITE MFD FILENAME=MASKS,SUFFIX=COM
-WRITE MFD SEGMENT=MASK1,SEGTYPE=S0
-WRITE MFD FIELD=CAT,,A8,$
-WRITE MFD FIELD=STU,,A3,$
-WRITE MFD FIELD=RTG,,A4,$
-WRITE MFD FIELD=ACT,,A4,$
-WRITE MASKS &MASK1
-WRITE MASKS &MASK2
-WRITE MASKS &MASK3

-**** process:
DEFINE FILE MASKS
  _BLANK /I3=BYTVAL(' X','I3');
  _DOLLAR/I3=BYTVAL('$X','I3');
  CAT/A8 = CTRAN(8,CAT,_BLANK,_DOLLAR,CAT);
  STU/A3 = CTRAN(3,STU,_BLANK,_DOLLAR,STU);
  RTG/A4 = CTRAN(4,RTG,_BLANK,_DOLLAR,RTG);
  MASK/A17= CAT | '.' | STU | '.' | RTG ;
  LIST/I3=LIST+1;
  _ELSE_ /A6=IF LIST EQ 1 THEN ' ' ELSE ' ELSE ';
  LINE/A80= _ELSE_ | 'IF (CHKFMT(15,COMBINED,''' | MASK | ''',''I3'') EQ 0) THEN ''' | ACT || '''';
END
FILEDEF $TEMP DISK $TEMP.FEX
TABLEF FILE MASKS
  PRINT LINE
  ON TABLE SAVE AS $TEMP
END
-RUN
DEFINE FILE MOVIES
  STUDIO/A3=EDIT(MOVIECODE,'$$$999');
  COMBINED/A17 = EDIT(CATEGORY,'99999999') | '.' | STUDIO | '.' | EDIT(RATING,'9999');
  ACTION/A4=
-*************
-INCLUDE $TEMP
-*************
  ELSE ' ';
END
TABLE FILE MOVIES
  PRINT CATEGORY MOVIECODE RATING TITLE
    BY ACTION UNDER-LINE
    BY COMBINED
  IF ACTION NE ' '
  FOOTING BOTTOM
"MASKS:"
"  &MASK1"
"  &MASK2"
"  &MASK3"
  ON TABLE PCHOLD FORMAT PDF
  ON TABLE SET STYLE *
    TYPE=REPORT,
      ORIENTATION=LANDSCAPE,
      FONT=COURIER,
      SIZE=10,
    $
  ENDSTYLE
END

On second thought ... there is a potential problem is using CHKFMT with the user-supplied masks:

In addition to $, the characters 9, A, and X have special meanings in the mask argument of CHKFMT (matching any numeric, alphabetic, or alphanumeric character, resp.) -- whereas in the mask values of
IF (or WHERE) var EQ 'mask'
they just denote themselves.

So you can get false matches, using CHKFMT, if those characters happen to appear in the user-suppkied mask values.