Subtotals using BTEQ in Teradata

Database
Enthusiast

Subtotals using BTEQ in Teradata

Hi,

I have used a BTEQ Script to incldue WITH BY clause to get Subtotals and exported the data to CSV file, but what happens is I have four columns 1st gives description,2nd -----, 3rd -------, and the 4th column gives values for which I need  subtotals .The subtotal is being displayed under 1st column and Is there anyway to format My BTEQ script such that I would get subtotals under the 4th column.Below is the script template I have used

Could you please suggest something

 

.SESSIONS 1 

.SET SESSION TRANSACTION BTET;

.SET ERROROUT STDOUT ; 

.LOGON hw41,hw41

.EXPORT RESET;

.export report file='c:\us1.csv';

.SET RECORDMODE OFF; 

.SET FORMAT OFF ; 

.SET TITLEDASHES OFF; 

.SET SEPARATOR ' ' ; 

.SET WIDTH 500;

 

sel

description,

location,

department,

sum(sales)

with sum(Sales) (title 'sum:')by department

from Values

where department between 100 and 300

group by 1,2,3;

 

.exit

2 REPLIES
Senior Apprentice

Re: Subtotals using BTEQ in Teradata

Use SET SEPARATOR '|' ALL; to add the separator to the summary lines.

Don't try tabs for separators, a BTEQ REPORT never writes control characters to an output file.

Better replace the proprietary WITH BY with Standard SQL GROUPING SETS and concat all the columns to get a real CSV file:

SELECT 
COALESCE(TRIM(description),'') || '|' ||
COALESCE(TRIM(location),'') || '|' ||
COALESCE(TRIM(department), '') || '|' ||
TRIM(sumsales)
FROM
(
SEL
description,
location,
department,
SUM(sales),
GROUPING (description) AS grp
FROM VALUES
WHERE department BETWEEN 100 AND 300
GROUP BY GROUPING SETS((1,2,3),(3))
) AS dt
ORDER BY
department, grp

Dieter

Enthusiast

Re: Subtotals using BTEQ in Teradata

Hi Dnoeth,

Thank you very much for the information...

I have tried Subtotals by using WITH BY on my SQL Assistant and it did not work, for which I started using BTEQ Script...

But now with GROUPING SETS I was able to get the Subtotals in SQL Assistant itself.. Thank u :)