Oracle. Leave blank line on composite key level break

JGFMK from

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 then thought what if I have a computed column. a is numeric, b & c are alphanumeric data types.. so I guess I could possibly use CONCAT plus TO_CHAR..

I am wondering if anyone can give me some pointers?

Cheers.

oracle formatting sqlplus

Answers

answered 6 days ago Alex Poole #1

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.