I have created a flat file containing 10 records. I have been running my so-called simple fex to display these records in a
little report.

Every time I run this, I get this result:

PAGE 1

SVC_CD
3370065
3370065
3370065
3370065
3370065

I don’t understand WHY.

Here is the data in the txt file named: VENT_CD.TXT

3370085
3160407
3160502
3370065
3370165
3370265
3370614
3370615
3370616
3370617

Here is the simple code to hopefully display the records in that file:

-SET &ECHO=ALL;
-*
FILEDEF VENT_CD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt
-RUN
-*
-SET &BEG1 = '20080901';
-SET &END1 = '20080901';
-*
DEFINE FILE T2CHGDTL
SVC_CD/A7 = EDIT(FEEDER_KEY,'$$$$$$9999999');
END
-*
TABLE FILE T2CHGDTL
PRINT SVC_CD
WHERE SVC_CD IN FILE VENT_CD;
-*IF RECORDLIMIT EQ 5
END
-RUN
-EXIT

Can anyone give me a hint?
Thx!

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

What is the value of FEEDER_KEY?

Try

...
PRINT
SVC_CD FEEDER_KEY
...

quote:

T2CHGDTL

Are there five rows in T2CHGDTL where the last 7 characters of FEEDER_KEY match the value 3370065 but no other matches?

quote:

FILEDEF VENT_CD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt
-RUN

This needs to have LRECL xx RECFM F defined as well.

i.e.,

FILEDEF VENT_CD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt LRECL xx RECFM F
-RUN

FEEDER_KEY seems to be a varchar; should be re-defined...
Tom

FILEDEF VENT_CD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt LRECL 80 RECFM F
-RUN
-EXIT


And I got this error:

(FOC358) ERROR ISSUING FILEDEF COMMAND FOR: filedef VENT_CD DISK
\\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt LRECL 80 RE

?

Try

FILEDEF VENT_CD DISK \\CLEVELANDPOINT\IBI\APPS\CIMDATA\VENT_CD.txt (LRECL 80 RECFM F

Don't ask me why, but there's a round bracket before LRECL.

Thanks Francis

I have been coding FOCUS since 1988 and some days I feel so dense.

I think the filedef is not the issue.

What is it that you want....
List all the records that are in the file?

If so, you need to create a master that describes the file.
If this file is just a way to select the data from the database file T2CHGDTL than give us more info.
Both Dojo and myself think your database only holds FEEDER_KEY fields that has a value of '3370065' in the last 7 positions.
Your report is working as expected, so what do YOU want.

Francis,

Thanks, MY BAD!

Tom

T2CHGDTL is a VERY LARGE DB2 Table, and I have isolated 10 codes that I want to use in a Rpt request. Those codes are stored in VENT_CD.TXT
flat file.

Ultimately I want a report that goes like:
DEFINE FILE T2CHGDTL
SVC_CD/A7 = EDIT(FEEDER_KEY,'$$$$$$9999999');
END

TABLE FILE T2CHGDTL
PRINT
Blah (FLDS FROM T2CHGDTL)
Blah
BY SVC_CD
WHERE SVC_CD in FILE VENT_CD;
END

My point in doing this was to just print the
ten values from the file.

I understand that if I create a MFD for this
VENT_CD file, I can just go against that for
a simple report to verify the values.

However, I thought I could get these 10 values
to display in a report by doing some code with
if SVC_CD NE LAST SVC_CD, etc., but that is not
working out either.

quote:

round bracket

Part of Focus's CMS heritage. In CMS FILEDEF, the trailing "options" are parenthesised, to set them off from the positional arguments; the closing parenthesis is optional, and typically omitted.

That still does not answer the question why you get these 5 records.

Waht do you get if you say

....
WHERE SVC_CD EQ '3370085'OR '3160407' OR '3160502' OR '3370065' OR '3370165' OR '3370265'
OR '3370614' OR '3370615' OR '3370616' OR '3370617';
.....

If you still get the same 5 records the problem appears not to be the filedef....

I'm with Frank - unless there is some other reason you need to use an external file, don't make it more complicated than it needs to be. If his code doesn't get you the right answer, there's some other problem. Sneaky suspicion if the FEEDER KEY field.

Having said that, it is not a very good idea to use a defined field in your selection criteria. In effect, it selects ALL records to the internal matrix, calculates the defined field, and THEN throws out the ones it doesn't need. Way too much I/O. Seems like it would work this way as well using the IN FILE method.

Last thought is to make sure that you are using the file that you think you are by checkking filedefs and locations with ? FI and WHENCE commands.

There is one thing I think we need to answer this question, the Master file, can you post it?

I also think that Tom and Francis are on the money, once we see the master file, an appropriate LRECL can be used.

Tom,
I suspect that the Data type for column "FEEDER_KEY" might be defined as Alpha in the T2CHGDTL master file.
Update the Data type to Integer and give a try.
E.g.
FIELD=FEEDER_KEY,FEEDER_KEY,I13,I13,MISSING=ON, TITLE='Feeder Key',$

QUERY TO DISPLAY FEEDER_KEY DATA:
[
TABLE FILE T2CHGDTL
PRINT
FEEDER_KEY
WHERE FEEDER_KEY CONTAINS '3370085' OR '3160407';
IF RECORDLIMIT EQ 5
END
-EXIT
]
ACTUAL FEEDER_KEY REPORT:
PAGE 1

FEEDER_KEY
8450003370085
8450003370085
8450003370085
8450003370085
8450003370085

MFD:
[
FILENAME=T2CHGDTL, SUFFIX=EDA , $
SEGMENT=T2CHGDTL, SEGTYPE=S0, $
FIELDNAME=COMPANY_CODE, ALIAS=COMPANY_CODE, USAGE=A5, ACTUAL=A5, $
FIELDNAME=ENCOUNTER_NUMBER, ALIAS=ENCOUNTER_NUMBER, USAGE=A20, ACTUAL=A20, $
FIELDNAME=MASTER_FILE_YEAR, ALIAS=MASTER_FILE_YEAR, USAGE=P5, ACTUAL=P3, $
FIELDNAME=DEPARTMENT, ALIAS=DEPARTMENT, USAGE=A10, ACTUAL=A10, $
FIELDNAME=DCM_PRODUCT_NODE, ALIAS=DCM_PRODUCT_NODE, USAGE=P12, ACTUAL=P7, $
FIELDNAME=FEEDER_SYSTEM, ALIAS=FEEDER_SYSTEM, USAGE=A5, ACTUAL=A5, $
FIELDNAME=FEEDER_KEY, ALIAS=FEEDER_KEY, USAGE=A20, ACTUAL=A20, $
FIELDNAME=DATE_OF_SERVICE, ALIAS=DATE_OF_SERVICE, USAGE=P9, ACTUAL=P5, $
FIELDNAME=CHARGE_DTL_POSTING_DATE, ALIAS=CHARGE_DTL_POSTING_DATE, USAGE=P9, ACTUAL=P5, $
FIELDNAME=ORDERING_PHYSICIAN, ALIAS=ORDERING_PHYSICIAN, USAGE=A10, ACTUAL=A10, $
FIELDNAME=TIME_OF_SERVICE, ALIAS=TIME_OF_SERVICE, USAGE=P7, ACTUAL=P4, $
FIELDNAME=CHGDTL_UB92_CODE, ALIAS=CHGDTL_UB92_CODE, USAGE=A10, ACTUAL=A10, $
FIELDNAME=CHGDTL_CPT4_CODE, ALIAS=CHGDTL_CPT4_CODE, USAGE=A9, ACTUAL=A9, $
FIELDNAME=CHARGE_DETAIL_MODIFIER_1, ALIAS=CHARGE_DETAIL_MODIFIER_1, USAGE=A2, ACTUAL=A2, $
FIELDNAME=CHARGE_DETAIL_MODIFIER_2, ALIAS=CHARGE_DETAIL_MODIFIER_2, USAGE=A2, ACTUAL=A2, $
FIELDNAME=CHARGE_DETAIL_MODIFIER_3, ALIAS=CHARGE_DETAIL_MODIFIER_3, USAGE=A2, ACTUAL=A2, $
FIELDNAME=DAY_OF_STAY, ALIAS=DAY_OF_STAY, USAGE=P6, ACTUAL=P4, $
FIELDNAME=FISCAL_YEAR, ALIAS=FISCAL_YEAR, USAGE=P5, ACTUAL=P3, $
FIELDNAME=FISCAL_PERIOD, ALIAS=FISCAL_PERIOD, USAGE=P3, ACTUAL=P2, $
FIELDNAME=CLINICAL_DAY_TYPE, ALIAS=CLINICAL_DAY_TYPE, USAGE=P4, ACTUAL=P3, $
FIELDNAME=FINANCIAL_DAY_TYPE, ALIAS=FINANCIAL_DAY_TYPE, USAGE=P4, ACTUAL=P3, $
FIELDNAME=OTHER_DAY_TYPE, ALIAS=OTHER_DAY_TYPE, USAGE=P4, ACTUAL=P3, $
FIELDNAME=PROCEDURE_QUANTITY, ALIAS=PROCEDURE_QUANTITY, USAGE=P13.2, ACTUAL=P7, $
FIELDNAME=PROCEDURE_ACTUAL_CHARGE, ALIAS=PROCEDURE_ACTUAL_CHARGE, USAGE=P13.2, ACTUAL=P7, $
FIELDNAME=DETAIL_QUANTITY, ALIAS=DETAIL_QUANTITY, USAGE=P17.6, ACTUAL=P9, $
FIELDNAME=DETAIL_ACTUAL_CHARGE, ALIAS=DETAIL_ACTUAL_CHARGE, USAGE=P13.2, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_CHARGE, ALIAS=DETAIL_STANDARD_CHARGE, USAGE=P13.2, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_VDL_COST, ALIAS=DETAIL_STANDARD_VDL_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_VDS_COST, ALIAS=DETAIL_STANDARD_VDS_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_VDO_COST, ALIAS=DETAIL_STANDARD_VDO_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_FDL_COST, ALIAS=DETAIL_STANDARD_FDL_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_FDE_COST, ALIAS=DETAIL_STANDARD_FDE_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_FDF_COST, ALIAS=DETAIL_STANDARD_FDF_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_FDO_COST, ALIAS=DETAIL_STANDARD_FDO_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_VI_COST, ALIAS=DETAIL_STANDARD_VI_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_STANDARD_FI_COST, ALIAS=DETAIL_STANDARD_FI_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_VDL_COST, ALIAS=DETAIL_ACTUAL_VDL_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_VDS_COST, ALIAS=DETAIL_ACTUAL_VDS_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_VDO_COST, ALIAS=DETAIL_ACTUAL_VDO_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_FDL_COST, ALIAS=DETAIL_ACTUAL_FDL_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_FDE_COST, ALIAS=DETAIL_ACTUAL_FDE_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_FDF_COST, ALIAS=DETAIL_ACTUAL_FDF_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_FDO_COST, ALIAS=DETAIL_ACTUAL_FDO_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_VI_COST, ALIAS=DETAIL_ACTUAL_VI_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=DETAIL_ACTUAL_FI_COST, ALIAS=DETAIL_ACTUAL_FI_COST, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=CHGDTL_FLAG_1, ALIAS=CHGDTL_FLAG_1, USAGE=A1, ACTUAL=A1, $
FIELDNAME=CHGDTL_FLAG_2, ALIAS=CHGDTL_FLAG_2, USAGE=A1, ACTUAL=A1, $
FIELDNAME=CHGDTL_USER_AMOUNT, ALIAS=CHGDTL_USER_AMOUNT, USAGE=P13.4, ACTUAL=P7, $
FIELDNAME=CHGDTL_USER_FIELD_1, ALIAS=CHGDTL_USER_FIELD_1, USAGE=A10, ACTUAL=A10, $
FIELDNAME=CHGDTL_USER_FIELD_2, ALIAS=CHGDTL_USER_FIELD_2, USAGE=A10, ACTUAL=A10, $
FIELDNAME=ANESTHESIA_BASE_UNITS, ALIAS=ANESTHESIA_BASE_UNITS, USAGE=P8.2, ACTUAL=P4, $
FIELDNAME=ANESTHESIA_DURATION_MIN, ALIAS=ANESTHESIA_DURATION_MIN, USAGE=P5, ACTUAL=P3, $
]

The problem isn't with VENT_CD or the OR option. You have multiple records with the equaling the first vent_cd. Try changing your code to SUM FEEDER_KEY BY FEEDER_KEY NOPRINT rather than PRINT and increase your RECORDLIMIT.

With a suffix of EDA this data is coming from another WF or Iway server.

Pat's probably right, the request will keep reading until the WHERE clause is fulfilled, up to 5 records after the filter.

quote:

My point in doing this was to just print the
ten values from the file.

If this is really the only reason IMHO it is not the best way.
You have a small text file with 10 numbers and to print it you use a big eda file from the server.
It's wasting report sources and time, even if it would take 10 seconds!
You can do a read and loop....