Display column names in export file bteq command

Question

I have SQL queries with bteq that sometimes return no data and it's normal. I want that the export file, even when the query returns no data, contains the columns names. How can I do that please?

Example of one of my queries:

tva()

{

    if [ -f "${lien}/test.txt" ]; then

        rm -f "${lien}/test.txt"

    fi

    touch "${lien}/test.txt"

 

    bteq <<EOF >>"${lien}/TVA-${DATE}-log.txt" 2>&1

                  .logmech LDAP

    .LOGON ${TDPID}/${USERID},${PASSWORD}

    .set width 2000

    .set titledashes off;

                  .SET FORMAT OFF;

    .set session charset 'UTF8'

    .export REPORT file="${lien}/test.txt"

    SELECT top 5 * FROM ${DB}.TABLENAME

    .EXPORT RESET

    .set recordmode off

    .logoff;

    .quit;

EOF

}

tva

I tried to change the recordmode to on, add format off etc but I didn't find any parameter that permits me to displays column name when in the export file when the query returns no data.

Thank you in advance.

There is no particularly elegant way to do this in any of the Teradata export tools. You can either build a header row in SQL and union that together with your actual data, or you can have a separate file with just your headers in it, and cat those 2 files together in your script. BTW, you would probably be better off using fast export or TPT to build an extract. — Andrew
– Andrew, Commented Apr 15 at 17:25

3aska · Accepted Answer · 2025-04-15 13:24:55Z

1

There's a specific parameter you can use to solve this problem: .SET HEADING ON. This parameter forces BTEQ to include column headers in the output file even when the query returns no rows.

Here's how you can modify your script:

bteq <<EOF >>"${lien}/TVA-${DATE}-log.txt" 2>&1
    .logmech LDAP
    .LOGON ${TDPID}/${USERID},${PASSWORD}
    .set width 2000
    .set titledashes off
    .SET FORMAT OFF
    .set session charset 'UTF8'
    .SET HEADING ON
    .export REPORT file="${lien}/test.txt"
    SELECT top 5 * FROM ${DB}.TABLENAME
    .EXPORT RESET
    .set recordmode off
    .logoff
    .quit
EOF

The .SET HEADING ON directive should ensure that column headers are included in your export file even when there are no rows returned by the query.

If that doesn't solve your problem, an alternative approach would be to modify your query to ensure it always returns at least one row, even if it's a dummy row that you can filter out later. However, the HEADING parameter should be the cleaner solution.

Just let me know if that works for you.

answered Apr 15 at 13:24

3aska

4711 gold badge5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gérard Menfin Apr 15 at 16:47

Unfortunately it didn't work. I will try changing the SQL query, thank you

3aska Apr 15 at 19:58

can you tell me what is the problem now? Maybe we can create a chat room to discuss what can we do about that

Andrew Apr 16 at 13:08

@3aska - there are a couple of problems with how you are trying to use SET HEADING. All it does is create a page heading, it has nothing to do with column headings. Secondly, it only takes a string argument (which is the text for the page heading). docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/…

Collectives™ on Stack Overflow

Display column names in export file bteq command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related