I have to produce a pipe-delimited file that has TWO header lines. The first line has file information, the second is the names of the fields. Here's a sample

FH|AROpenRepairOrders|1|DL1|2012-08-11 00:15:06|Joe.Doe@hotmail.com|

Record Identifier|Service Order Number|Creation Date Time|Buyer ID|Part Number|Manufacturer Code|Unit Of Measure|Order Quantity|

HH|RO1234533|2012-01-11 00:15:06|JDoe445|320115|81205|EA|1|
HH|RO1234534|2012-01-11 12:15:06|JDoe442|320115|81205|EA|1|
HH|RO6234535|2012-01-11 13:15:06|JDoe440|320115|81205|EA|1|

I've tried using FILEDEF (APPEND with two HOLD commands. It seems to take either the first heading or the second. I've tried -WRITE and can't get it to work. Here's the current version of code:

FILEDEF H001 DISK H001.FTM
-RUN

SQL SQLORA PREPARE SQLOUT FOR
SELECT 'FH', 'AROpenRepairOrders','1.0','ACA',TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'),'joe.doe@hotmail.com|' FROM DUAL
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER NO
END
-RUN
-*
SQL SQLORA ALTER SESSION SET CURRENT_SCHEMA = ODB;
END
SQL SQLORA PREPARE SQLOUT FOR
.... long SQL here

FILEDEF H001 DISK H001.FTM (APPEND
-RUN

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER YES
END
-RUN

This gives me the file header line, but not the field-names line. I am a neophyte and will be grateful for any help.

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

You need to issue the FILEDEF twice, once to start, then once with APPEND for the rest:

FILEDEF H001 DISK H001.FTM
-RUN

TABLE FILE CAR
PRINT
COMPUTE HEADER/A100 = COUNTRY || '|' || 'JOE.DOE@HOTMAIL.COM';

WHERE COUNTRY EQ 'ENGLAND';

ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER NO
END
-RUN

FILEDEF H001 DISK H001.FTM (APPEND

TABLE FILE CAR
PRINT *
WHERE COUNTRY EQ 'ENGLAND'

ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER YES
END
-RUN

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

Thanks for your reply, Francis. I copied your code and the result I get is:

ENGLAND|JOE.DOE@HOTMAIL.COM
ENGLAND|JAGUAR|V12XKE AUTO|CONVERTIBLE|2|7427|8878|0|190|66|48|3435|105.0|18.0|241|5750|16|7
ENGLAND|JAGUAR|XJ12L AUTO|SEDAN|5|11194|13491|12000|199|70|54|4200|112.8|24.0|241|5750|9|9
ENGLAND|JENSEN|INTERCEPTOR III|SEDAN|4|14940|17850|0|188|69|53|4000|105.0|24.0|385|4700|11|8
ENGLAND|TRIUMPH|TR7|HARDTOP|2|4292|5100|0|165|66|50|2241|85.0|14.5|90|5000|25|0

I need to also produce a line after the first, containing the field names.

COUNTRY|CAR|MODEL|BODYTYPE|SEATS|DEALER_COST|RETAIL_COST|SALES|LENGTH|WIDTH|HEIGHT|WEIGHT|WHEELBASE|FUEL_CAP|BHP|RPM|MPG|ACCEL

Any ideas why it doesn't appear? It seems like the HEADER YES option should have produced it. If I comment out the part that produces the first line and just use the second, the field names ARE produced. So what's happening?

It is strange, but it works for HOLD, but not for PCHOLD. I would open a case with Tech Support, providing the two examples.

Meanwhile, the workaround might be to use HOLD - if you can access the location on the reporting server where the FILEDEF can create a file...



This works:

FILEDEF H001 DISK baseapp/h001dfix.ftm
-RUN

TABLE FILE CAR
PRINT
COMPUTE HEADER/A100 = COUNTRY || '|' || 'joe.doe@hotmail.com';

WHERE COUNTRY EQ 'ENGLAND';

ON TABLE HOLD AS H001 FORMAT DFIX DELIMITER | HEADER NO
END
-RUN

FILEDEF H001 DISK baseapp/h001dfix.ftm (APPEND

TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
LENGTH
WEIGHT
HEIGHT
WHERE COUNTRY EQ 'ENGLAND'

ON TABLE HOLD AS H001 FORMAT DFIX DELIMITER | HEADER YES
END
-RUN

Generates:

ENGLAND|joe.doe@hotmail.com
COUNTRY|CAR|MODEL|LENGTH|WEIGHT|HEIGHT
ENGLAND|JAGUAR|V12XKE AUTO|190|3435|48
ENGLAND|JAGUAR|XJ12L AUTO|199|4200|54
ENGLAND|JENSEN|INTERCEPTOR III|188|4000|53
ENGLAND|TRIUMPH|TR7|165|2241|50

This does not work:

FILEDEF H001 DISK h001dfix.ftm
-RUN

TABLE FILE CAR
PRINT
COMPUTE HEADER/A100 = COUNTRY || '|' || 'joe.doe@hotmail.com';

WHERE COUNTRY EQ 'ENGLAND';

ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER NO
END
-RUN

FILEDEF H001 DISK h001dfix.ftm (APPEND

TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
LENGTH
WEIGHT
HEIGHT
WHERE COUNTRY EQ 'ENGLAND'

ON TABLE PCHOLD AS H001 FORMAT DFIX DELIMITER | HEADER YES
END
-RUN

Generates:

ENGLAND|joe.doe@hotmail.com
ENGLAND|JAGUAR|V12XKE AUTO|190|3435|48
ENGLAND|JAGUAR|XJ12L AUTO|199|4200|54
ENGLAND|JENSEN|INTERCEPTOR III|188|4000|53
ENGLAND|TRIUMPH|TR7|165|2241|50

I guess as a workaround you could use the two HOLDs that Francis talks about. Then create a generic master so you can PCHOLD the output.

  
-* Allocate the flat file
FILEDEF H001 DISK h001.ftm (APPEND
-RUN

TABLE FILE CAR
PRINT
   COMPUTE HEADER/A100 = COUNTRY || '|' || 'JOE.DOE@HOTMAIL.COM';

WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS H001 FORMAT DFIX DELIMITER | HEADER NO
END
-RUN


TABLE FILE CAR
PRINT
   COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES 
   LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL

WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS H001 FORMAT DFIX DELIMITER | HEADER YES
END
-RUN


-* Create master for flat file
FILEDEF HMAST DISK hdata.mas
-RUN

-WRITE HMAST FILENAME=HDATA, SUFFIX=DFIX, DATASET=h001.ftm, $
-WRITE HMAST SEGMENT=HDATA, SEGTYPE=S0, $
-WRITE HMAST FIELDNAME=COLUMN1, ALIAS=COLUMN1, USAGE=A4000, ACTUAL=A4000, $
-RUN


-* Output the flat file using PCHOLD
TABLE FILE HDATA
PRINT
   COLUMN1 AS ''
ON TABLE PCHOLD FORMAT DFIX DELIMITER | HEADER NO
END
-RUN

Thank you Max and Francis, sorry for the delay responding.

Concatenating two files using HOLD, not PCHOLD, worked. I didn't know where to find the output but did eventually (I told you I was new at this).