All Forums Database
u156531 6 posts Joined 10/14
02 Oct 2014
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

frnewbrough 41 posts Joined 03/08
02 Oct 2014

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

 

u156531 6 posts Joined 10/14
06 Oct 2014

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

dnoeth 4628 posts Joined 11/04
06 Oct 2014

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"
 

Dieter

You must sign in to leave a comment.