I am having a problem with all the records displaying in a report when I create a synonym off a Delimited Flat File (.csv). I am able to create the synonym without a problem, but when I run the report it is only showing 28 of the 392 records.
All the records are in the file because if I create the synonym on the same file, only as "TAB" delimited instead of .csv, all the data appears. The only problem with this is that all the data comes over in a single field.
Has anyone experienced anything similar to this?

Thanks,

Rob

Does any of your data include single or double quote marks (or apostrophes)? Are your fields the same size for all records? Do you have an lrecl on the filedef? Do you have any missing fields?

I'm going to take a guess and say if you are trying to write a synonym (aka Master File Description (MFD)) for a comma separated file, make sure there are no commas in your data.

Just a thought.

Thanks for the quick response Pat and Mickey! To give you a bit of background:
We have SQL Server 2k and Oracle databases...our adapter is for Oracle. I am taking data from our SQL Server and pulling it out as a .csv. There are no quotes or commas in the data itself. The field sizes are not uniform (some A34, A4, A12...they are all text, no numbers) and there ARE some fields with missing data. Pat - I am not familar with Irecl on the filedef.
The file itself looks perfect in a report with the exception of it only displaying 28 of the 392 rows.

Thank you both for your help.

Rob

You will need to do some good old fashion debugging here.

The first thing I would check is to see if the records you are getting are the first 28 or are random from the file.

Hi Mickey,

Yes, the records being displayed are the first 28, not random. I'll look over the text file for any anomalies.

The master file looks like this, if it's of any use....

FILENAME=MASTERFILE, SUFFIX=DFIX ,
DATASET=/data/data01/ibi/apps/sampleapp/masterfile.txt, $
SEGMENT=MASTERFILE, SEGTYPE=S0, $
FIELDNAME=FIELD_1, ALIAS= , USAGE=A34, ACTUAL=A34, $
FIELDNAME=FIELD_2, ALIAS= , USAGE=A4, ACTUAL=A4, $
FIELDNAME=FIELD_3, ALIAS= , USAGE=A12, ACTUAL=A12, $
FIELDNAME=FIELD_4, ALIAS= , USAGE=A15, ACTUAL=A15, $
FIELDNAME=FIELD_5, ALIAS= , USAGE=A30, ACTUAL=A30, $
FIELDNAME=FIELD_6, ALIAS= , USAGE=A17, ACTUAL=A17, $
FIELDNAME=FIELD_7, ALIAS= , USAGE=A14, ACTUAL=A14, $
FIELDNAME=FIELD_8, ALIAS= , USAGE=A10, ACTUAL=A10, $
FIELDNAME=FIELD_9, ALIAS= , USAGE=MDYY, ACTUAL=A10, $
FIELDNAME=FIELD_10, ALIAS= , USAGE=A17, ACTUAL=A17, $
FIELDNAME=FIELD_11, ALIAS= , USAGE=A9, ACTUAL=A9, $
FIELDNAME=DELIMITER, ALIAS=',', USAGE=A1, ACTUAL=A1, $


Thanks again,

Rob

Rob

Try using the MISSING=ON attribute in the mfd.

FIELDNAME=FIELD_1, ALIAS= , USAGE=A34, ACTUAL=A34, MISSING=ON, $

Rob,

Could you post a few records between CODE tags?

quote:

We have SQL Server 2k and Oracle databases...our adapter is for Oracle. I am taking data from our SQL Server and pulling it out as a .csv.

Is there a reason for holding the data as a .csv file? Is your file fixed and .txt or CVS?

FILENAME=MASTERFILE, SUFFIX=DFIX ,
DATASET=/data/data01/ibi/apps/sampleapp/masterfile..txt, $


This may be legitimate, but MDYY is a "SMART DATE" and A10 implies gregorian with the slashes.
FIELDNAME=FIELD_9, ALIAS= , USAGE=MDYY, ACTUAL=A10, $

FIELDNAME=DELIMITER, ALIAS=',', USAGE=A1, ACTUAL=A1, $
Does the DELIMITER field actually exist in the data or is this a defined field? Try changing the ALIAS to CMMA. An ALIAS is an alternate name not a value.

Thank you all for your help with this. I called tech support and through the traces it was determined that the field sizes were not compatible with the data in the next field (FOC1271)...meaning that it showed records 1-28, but in record 29 the address was larger than the specified field size. I adjusted the all the field sizes and all the data now appears.

===============================

Original Masterfile (only showed records 1 - 28):

FILENAME=MASTERFILE, SUFFIX=DFIX ,
DATASET=/data/data01/ibi/apps/sampleapp/masterfile.txt, $
SEGMENT=MASTERFILE, SEGTYPE=S0, $
FIELDNAME=FIELD_1, ALIAS= , USAGE=A34, ACTUAL=A34, $
FIELDNAME=FIELD_2, ALIAS= , USAGE=A4, ACTUAL=A4, $
FIELDNAME=FIELD_3, ALIAS= , USAGE=A12, ACTUAL=A12, $
FIELDNAME=FIELD_4, ALIAS= , USAGE=A15, ACTUAL=A15, $
FIELDNAME=FIELD_5, ALIAS= , USAGE=A30, ACTUAL=A30, $
FIELDNAME=FIELD_6, ALIAS= , USAGE=A17, ACTUAL=A17, $
FIELDNAME=FIELD_7, ALIAS= , USAGE=A14, ACTUAL=A14, $
FIELDNAME=FIELD_8, ALIAS= , USAGE=A10, ACTUAL=A10, $
FIELDNAME=FIELD_9, ALIAS= , USAGE=MDYY, ACTUAL=A10, $
FIELDNAME=FIELD_10, ALIAS= , USAGE=A17, ACTUAL=A17, $
FIELDNAME=FIELD_11, ALIAS= , USAGE=A9, ACTUAL=A9, $
FIELDNAME=DELIMITER, ALIAS=',', USAGE=A1, ACTUAL=A1, $

Modified Masterfile (Shows all 392 records):

FILENAME=MASTERFILE, SUFFIX=DFIX ,
DATASET=/data/data01/ibi/apps/sampleapp/masterfile.txt, $
SEGMENT=MASTERFILE, SEGTYPE=S0, $
FIELDNAME=FIELD_1, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_2, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_3, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_4, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_5, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_6, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_7, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_8, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_9, ALIAS= , USAGE=MDYY, ACTUAL=A10, MISSING=ON $
FIELDNAME=FIELD_10, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=FIELD_11, ALIAS= , USAGE=A50, ACTUAL=A50, MISSING=ON $
FIELDNAME=DELIMITER, ALIAS=',', USAGE=A1, ACTUAL=A1, MISSING=ON $


Thanks again,

Rob