I want to know the export format/option in BTEQ which can export data with new line.
Example to explain what is expected - Below is the sample code I am running in BTEQ
.export report file <filename>;
Output is : AB
I want the output to be exported as
Please suggest how I can achieve this.
I think if you want two rows in your output you'll have to have two rows selected.
.export report ...
SELECT 'A'; SELECT 'B';
Ok, so change the select to something like:
.export report ... SELECT 'A' FROM DBC.DATABASESV WHERE DATABASENAME = USER UNION ALL SELECT 'B' FROM DBC.DATABASESV WHERE DATABASENAME = USER ; .export reset;
Why do you need it in one select?
I've done what you're trying to do many times over the years and I've always ended up writing one select for each line of generated code.
The only alternative that I can think of requires some non-BTEQ coding.
Generate the data as you originall tried to do.
Export the results to a data file.
Read that data file using a program or script as appropriate and split each line in the file on CR/LF. Write the resulting data ot a new file.
The new file now contains your SQL code.
Thanks for all your inputs Dave. I am managing currently with a solution similar to what you proposed :) .
But I still thought there should be a way which should avoid non-BTEQ scripting to achieve this.