Creating Header,Detail and Trailer records from one SQL statement

Database
Enthusiast

Creating Header,Detail and Trailer records from one SQL statement

Using SQL I'm trying to create the output to a file. The output results need to contain a Header, Detail and Trailer records. I've tried to use "Union all" but since the column attributes are different the SQL fails. Does anyone know how to union or append the results from sql statements into one result set?

Thanks

3 REPLIES
Teradata Employee

Re: Creating Header,Detail and Trailer records from one SQL statement

If you really wanted to use a union all you could. You would just have to convert everything to a long varchar concatenating things together.

You could also just run three different processes exporting and appending to a file or export each piece and then combine them. 

Below is an example of a sql using a union all.

SELECT section,columnid,view_txt
FROM
(
SELECT
1 (INT) AS section, ColumnId,
CASE WHEN ROW_NUMBER () OVER(
ORDER BY ColumnId) = 1 THEN (
CASE
WHEN ColumnType='DA' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || ')) '||TRIM(ColumnName)
WHEN ColumnType='D' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT TRIM('||TRIM(ColumnName)||'(VARCHAR('||'40'||'))) '||TRIM(ColumnName)
WHEN ColumnType IN ('CV','CF') THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT OREPLACE(OREPLACE(TRIM(TRANSLATE('||TRIM(ColumnName)||' USING LATIN_TO_UNICODE WITH ERROR)),CHR(13),''''),CHR(10),'''') (VARCHAR('||TRIM(ColumnLength+2 (FORMAT 'Z(20)') )||')) '||TRIM(ColumnName)
ELSE 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName)
END )
ELSE ','||(
CASE
WHEN ColumnType='DA' THEN TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || ')) '||TRIM(ColumnName)
WHEN ColumnType='D' THEN 'TRIM('||TRIM(ColumnName)||'(VARCHAR('||'40'||'))) '||TRIM(ColumnName)
WHEN ColumnType IN ('CV','CF') THEN 'OREPLACE(OREPLACE(TRIM(TRANSLATE('||TRIM(ColumnName)||' USING LATIN_TO_UNICODE WITH ERROR)),CHR(13),''''),CHR(10),'''') (VARCHAR('||TRIM(ColumnLength+2 (FORMAT 'Z(20)') )||')) '||TRIM(ColumnName)
ELSE ColumnName
END )
END (VARCHAR(1000)) AS view_txt

FROM DBC.COLUMNS

WHERE
DatabaseName =TRIM(:TGT_DB)
AND
TABLENAME=TRIM(:TGT_TB)
UNION
ALL
SELECT *
FROM (
SELECT 2 (INT) AS section,1 AS ColumnId, ' FROM '||TRIM(:TGT_DB)||'.'||TRIM(:TGT_TB)||'' AS view_txt) a ) b ) view_def
ORDER BY section,columnid
Enthusiast

Re: Creating Header,Detail and Trailer records from one SQL statement

Thank you for this example. I tried to mimic the SQL for my environment but I'm not getting it to work. I keep getting a syntax error.

Would it be possilbe for you to simplify this example to the bare essentials?  

Thanks, Mark

Junior Contributor

Re: Creating Header,Detail and Trailer records from one SQL statement

What tool do you use for exporting?

- both BTEQ and FastExport append the data of multiple selects by default

- SQL Assistant got an option "write all answer set to a single file"