Can somebody tell me what the BY statements have to be like in order to use IN FILE? Its weird because I have been trying something like this:

TABLE FILE T2ENCNTR
SUM CNT.ENCOUNTER_NUMBER AS 'DOCPATS'
LOS AS 'DOCLOS'
BY DRG
BY MONTH
WHERE PCDOCNUM IN ('06077','21185','05960','11331','22144','05707','10506');
WHERE DRG NOT-FROM '370' TO '375';
WHERE DISCHARGE_DATE FROM '&BEG1' TO '&END1' ;
WHERE COMPANY_CODE EQ '&HOSP' ;
WHERE ACTUAL_TOTAL_CHARGE GT 0 ;
WHERE EXPIRED_CODE NE 'Y';
WHERE DRG NE ' ';
WHERE ENCTR_USER_ALPHA_3 NE 'Q-VSIT';
ON TABLE HOLD AS BW_HOLD1
END

TABLE FILE T2ENCNTR
SUM CNT.ENCOUNTER_NUMBER AS 'TOTPATS'
AVE.LOS AS 'PCMHLOS'
BY DRG
BY MONTH
WHERE DRG IN FILE BW_HOLD1 ;
WHERE DISCHARGE_DATE FROM '&BEG1' TO '&END1' ;
WHERE COMPANY_CODE EQ '&HOSP' ;
WHERE ACTUAL_TOTAL_CHARGE GT 0 ;
WHERE EXPIRED_CODE NE 'Y';
WHERE DRG NE ' ';
WHERE ENCTR_USER_ALPHA_3 NE 'Q-VSIT';
-*ON TABLE HOLD AS DRG_ALOS
ON TABLE SET ONLINE-FMT EXL2K
END

and I get this (line 71 is the WHERE DRG IN FILE BW_HOLD1 ):

0 ERROR AT OR NEAR LINE 71 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: ?¬éQI¥@
0 ERROR AT OR NEAR LINE 71 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: WHERE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Mark1,

I've never tried using IN FILE against a file containing multiple columns. So I'm not sure whether that might cause you problems. I do know you need FORMAT ALPHA on your HOLD statement: ON TABLE HOLD AS BW_HOLD1 FORMAT ALPHA. (In theory, BINARY will also work, but I haven't tried that.) If that does not work, I'd suggest creating a hold file with a single field. And if THAT doesn't work, create a SAVE file, instead of a HOLD file.

Cool. thanks. I will give it a try on Monday and reply back.

I used this several times, but there are some rules and restrictions.
1) The hold file only needs to have one field, so in this case only the field DRG.
2) add the -RUN command after the table that creates the hold file.
3) the already mentioned "SAVE AS XXX FORMAT ALPHA"
4) the number of records or the total file size has a limit of 2K (I'm not sure about the exact size).

Hope this helps....

As a clarification, when using a TABLE request to create a screening file for use in 'WHERE field in FILE filename':

Use:

ON TABLE HOLD (default binary format, no AS name). Alpha or numeric comparison. File may contain multiple fields (though for clarity maybe it shouldn't), comparison field must be the first field. (WHERE field in FILE HOLD). This is my preferred option as it causes no issues. WF recognises this as a HOLD file and acts accordingly.

ON TABLE HOLD FORMAT ALPHA (no AS name). For alpha comparison only, not numeric fields. File may contain multiple fields, comparison field must be the first field. (WHERE field in FILE HOLD).

ON TABLE HOLD AS fileName (default binary format). For alpha comparison only, not numeric fields. File must contain only a single field. Note that using just BY field places 2 fields into the HOLD file, use SET HOLDLIST=PRINTONLY. (WHERE field in FILE fileName).

ON TABLE HOLD AS fileName FORMAT ALPHA. Alpha or numeric comparison. File must contain only a single field for Alpha comparison, though strangely for numeric fields the file may contain the 2 fields created when using BY numericField. (WHERE field in FILE fileName).

ON TABLE SAVE AS fileName (default alpha format). The same as ON TABLE HOLD AS fileName FORMAT ALPHA.

ON TABLE SAVE AS fileName FORMAT BINARY. The same as ON TABLE HOLD AS fileName (default binary format).

ON TABLE SAVE. The same as ON TABLE HOLD AS fileName FORMAT ALPHA.

ON TABLE SAVE FORMAT BINARY. The same as ON TABLE HOLD AS fileName (default binary format).


File size limited to 'approximately 16,000 bytes' [sic] when using WHERE.

If a file is empty, then no screening is applied, i.e. all records are returned based on other screening, if any.

I would recommend that, for ease, Mark changes:
ON TABLE HOLD AS BW_HOLD1 to ON TABLE HOLD
and
WHERE DRG IN FILE BW_HOLD1 ; to WHERE DRG IN FILE HOLD;

Causing a slight aside for a moment.

There does not need to be a -RUN in between the TABLE requests. There seems to be a self-perpetuating myth that a -RUN is required under many and various circumstances. Though a -RUN can be placed in between requests if you really want to (some before me have even considered -RUN to be almost the work of the devil), the only time a -RUN is essential is when a piece of Dialogue Manager code relies on a piece of none-DM code to work prior to that DM command, and the DM does not recognise this state.
e.g.

TABLE FILE CAR
PRINT CAR
IF COUNTRY EQ 'PORTUGAL'
END
-IF &LINES EQ 0 GOTO MYLABEL;
-TYPE Got &LINES lines
TABLE FILE...
.
.
-MYLABEL

The reference to &LINES causes the TABLE request to run (actually everything in FOCSTACK is processed), zero records returned, flow passes to MYLABEL, TYPE statement and subsequent TABLE bypassed. No -RUN required. You can see this action with a -SET &ECHO=ALL;, where the -IF &LINES EQ 0 GOTO MYLABEL; is encountered, no DM action is initially taken, FOCSTACK is processed and only then is the line actually processed.

One of the very few times that a -RUN is essential is when a FILEDEF/ALLOC is followed by a -READ or -WRITE,e.g.

FILEDEF file DISK path/file.dat
-RUN
-READ file &variable.A4

will fail if the -RUN is omitted.

Otherwise the none DM commands (anything that does not have a - at the start of the line) get placed into FOCSTACK and are run at the end of the procedure (or at an -EXIT), in the order they are placed in the FOCSTACK, each line completing before the next line is read. An END statement, amongst others, will cause the commands drawn from FOCSTACK to be executed and FOCSTACK processing will pause until the execution is complete.

I don't mean to lecture (or maybe I do!), but I hope this clarifies things for some people.

(08:29 - cleared up a couple of small typos)

This message has been edited. Last edited by: Alan B,

Hi Alan

masters always lecture....it's their habit.

but we all can still learn a lot.

Alan,

I have used "in file" for quite a while and most of what you explained was news to me. This was one much appreciated lecture. Keep them coming.

Glenda

Lecture anytime.

I'd like to add one more little tip on this subject. I have for years used the same multi-field SAVE file in both IF statements and in DECODE statements. For the multi-field SAVE file to work, the fields have to be separated with a blank.

jgelona

Thanks for that addition.

For the example I quoted, WHERE field IN FILE HOLD, for a multi-field HOLD a blank field is NOT required. For SAVE files a blank field could be used between fields, but I wanted to keep this relatively simple.

For me, using a HOLD file is the simplest approach, without having to be concerned about any other factors.

Creating a file for a DECODE is another matter entirely. You must not forget the quotes (if required) and the blank (or comma) in between.