1

Good day, Stack Overflow folks. I have a question regarding some SQL code that I'm updated for some regulatory processes that my team has. When I run the following code, I get the export set up just fine, however, I am getting several header rows and several rows of nothing. The SQL code in question is below:

Set Heading On
Set Colsep '|'
Set NumFormat 999999999999.99
Set Echo Off
Spool 'X:\Cool\Drive\Space\Yo\Output.csv' Replace;

Select …
From …
Group By …
;

Spool Off;

The output looks something like this:

A|      B|     C|...
-|-------|------|...

with multiple instances of those rows repeating.

Does anyone out there know how to stop this from happening, and how I can trim the outputs so we don't have a bunch of white spaces before the actual data starts printing?

Thank you!

jarlh
  • 42,561
  • 8
  • 45
  • 63
A_Pirates_Favorite_Letter
  • 141
  • 1
  • 12

1 Answers1

2

You need to add two things

SQL> set underline off
SQL> set pagesize 100

PAGESIZE says, how many rows to print before you print the header column names again. If you only want to see those once, set the pagesize larger than the number of rows.

Here's my query -

SQL> set heading on
SQL> set colsep '|'
SQL> set numformat 999999999999.99
SQL> select sum(salary), department_id
  2  from employees
  3  group by department_id
  4  ;

And if I run that -

     SUM(SALARY)|   DEPARTMENT_ID
       105970.33|      100.00
        51214.47|       30.00
        14380.48|
       119020.33|       90.00
        39014.85|       20.00
        20532.81|       70.00
        41680.87|      110.00
       321867.32|       50.00
       626338.39|       80.00
        13355.08|       40.00
        59187.52|       60.00
         8228.13|       10.00

12 rows selected.
thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120