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
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:
COALESCE(TRIM(description),'') || '|' ||
COALESCE(TRIM(location),'') || '|' ||
COALESCE(TRIM(department), '') || '|' ||
GROUPING (description) AS grp
WHERE department BETWEEN 100 AND 300
GROUP BY GROUPING SETS((1,2,3),(3))
) AS dt
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 :)