I want the output of my SQL to leave a blank line when any of three columns (a b or c below) change.
So if the table had columns a b c and d, you end up with a report like this:
a b c d #<- column names ------- 1 a a a #<- V - data itself 1 a a b #<- level break here because 1 a b is different from 1 a a 1 a b c
From Googling, I have seen BREAK might solve this. But from what I can make out this pertains to one column.
I am wondering if anyone can give me some pointers?
Cheers.oracle formatting sqlplus
You can break on more than one column:
SQL> break on a skip 1 duplicates on b skip 1 duplicates on c skip 1 duplicates SQL> select * from your_table A B C D ---------- - - - 1 a a a 1 a a b 1 a b c 3 rows selected.
If you issue a plain
break it shows what is set:
SQL > break break on a skip 1 dup on b skip 1 dup on c skip 1 dup
Another option is to generate an extra column expression of your composite key, break on that, and set it not to display:
SQL> break on composite skip 1 SQ>> column composite noprint SQL> select t.*, a||':'||b||':'||c as composite from your_table t; A B C D ---------- - - - 1 a a a 1 a a b 1 a b c 3 rows selected.
which has the advantage of not showing multiple blank lines if more than one column changes at the same time.
I've separated the values with a colon; the idea of that is to use a character that doesn't appear in the values themselves, to avoid an accidental clash. If any of the columns could actually have a colon then pick something else more obscure.