Is it possible to BREAK ON a column in addition to what is specified in SQL * Plus?

Question

This is difficult to explain, so I will try to show what I am using as an example. Please note that I am not asking if it is possible to use multiple columns in an instruction BREAK- I know that it is.

Suppose I have a request:

SELECT  invoice_no, invoice_date, vendor, account, amount
FROM    invoice
ORDER   BY vendor, invoice_no, account

And let the result set from this:

INVOICE_NO INVOICE_DATE VENDOR  ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001       30-JAN-2009  Alpha   1000     50.00
0003       30-JAN-2009  Alpha   1000    125.00
0003       30-JAN-2009  Alpha   3000     33.33
0006       02-FEB-2009  Alpha   2000     40.00
0005       31-JAN-2009  Bravo   1000     40.00
0002       30-JAN-2009  Charlie 2000    120.75
0002       30-JAN-2009  Charlie 3000      5.00
0004       30-JAN-2009  Charlie 1000    900.50

So, you can see that some vendors have multiple invoices, and some invoices have multiple accounts.

To hide duplicate vendor names and account numbers, I can use the SQL * Plus command BREAK:

BREAK ON vendor ON invoice_no

What creates this result set:

INVOICE_NO INVOICE_DATE VENDOR  ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001       30-JAN-2009  Alpha   1000     50.00
0003       30-JAN-2009          1000    125.00
           30-JAN-2009          3000     33.33
0006       02-FEB-2009          2000     40.00
0005       31-JAN-2009  Bravo   1000     40.00
0002       30-JAN-2009  Charlie 2000    120.75
           30-JAN-2009          3000      5.00
0004       30-JAN-2009          1000    900.50

. , -. , :

BREAK ON vendor ON invoice_no ON invoice_date

- 0003 0004 , , - :

INVOICE_NO INVOICE_DATE VENDOR  ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001       30-JAN-2009  Alpha   1000     50.00
0003                            1000    125.00
                                3000     33.33
0006       02-FEB-2009          2000     40.00
0005       31-JAN-2009  Bravo   1000     40.00
0002       30-JAN-2009  Charlie 2000    120.75
                                3000      5.00
0004                    Charlie 1000    900.50

, ( AND):

BREAK ON vendor ON invoice_no AND invoice_date

, , invoice_no, invoice_date ( , - -):

INVOICE_NO INVOICE_DATE VENDOR  ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001       30-JAN-2009  Alpha   1000     50.00
0003       30-JAN-2009          1000    125.00
                                3000     33.33
0006       02-FEB-2009          2000     40.00
0005       31-JAN-2009  Bravo   1000     40.00
0002       30-JAN-2009  Charlie 2000    120.75
                                3000      5.00
0004       30-JAN-2009  Charlie 1000    900.50

0003 0004.

SQL * Plus?

Answer 1

, SQL * Plus . sed, , :

sed -re 's/^( {11})[0-9A-Z-]{11}(.+)$/\1           \2/'

Answer 2

- :

DUMMY NOPRINT
BREAK ON DUMMY ON vendor ON invoice_no
SELECT CONCAT (, invoice_no) DUMMY, invoice_no, invoice_date, , ,
-
ORDER BY vendor, invoice_no, account

column DUMMY , .

DUMMY - , , - ,

"ON vendor ON invoice_no" DUMMY. DUMMY ..

Answer 3

You can BREAK ONexpress, therefore, turning the fields of interest into strings and combining them with ||, you can assemble an expression that allows you to split "both values".