Is it possible to BREAK ON a column in addition to the one specified in SQL*Plus?

Question

This one's tough to explain, so I'll try to show what I'm after using examples. Please note that I'm not asking if it's possible to use multiple columns in a BREAK statement--I know that it is.

Suppose I have a query like the following:

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

And suppose the result set from that is:

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 the duplicate vendor names and invoice numbers, I can use SQL*Plus's BREAK command like so:

BREAK ON vendor ON invoice_no

Which produces 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

So far, so good. I would also like to hide duplicate invoice dates, so that just the first date for each invoice is shown. However, if I use this command:

BREAK ON vendor ON invoice_no ON invoice_date

It will go too far and hide the date for invoices 0003 and 0004, just because they are the same as the previous invoices from their respective vendors:

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

What I'd really like is a command syntax like the following (I made up the term AND):

BREAK ON vendor ON invoice_no AND invoice_date

The intention is that, whenever it breaks on invoice_no, to also break on invoice_date (since I know that a single invoice number can't have two invoice dates):

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

And now the dates are correctly displayed for invoices 0003 and 0004.

Is there any way to accomplish this in SQL*Plus?

Solution 1

It doesn't seem like SQL*Plus can do the trick. I ended up using sed to get rid of the invoice dates whenever the invoice number was blank:

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

Solution 2

You want something like this:

column DUMMY NOPRINT
BREAK ON DUMMY ON vendor ON invoice_no
SELECT CONCAT (vendor, invoice_no) DUMMY, invoice_no, invoice_date, vendor, account,amount
FROM invoice
ORDER BY vendor, invoice_no, account

where you are setting the column DUMMY as not printed and then defining it in your select as a concatenation of the two fields you need to check.

DUMMY is just an arbitrary name, but common use for cases like this where you need to calculate something but not display it

The extra "ON vendor ON invoice_no" allow you to control dups on those cols separate from the DUMMY break. Similarly you could compute using DUMMY to get totals etc.

Solution 3

You can BREAK ON an expression, so by turning the fields of interest into strings and concatenating them with || you should be able to put together an expression that lets you break on "both of their values".

Comments

[Alex Martelli]

I believe (but have no SQL*Plus at hand to test) that you can "BREAK ON (invoiceno||TO_CHAR(invoice_date))" -- that is, BREAK ON can use an expression, not just a column name.

[yukondude]

I tried that, but it didn't work. And from looking at it, I'm not sure how SQL*Plus would know to stop printing duplicates for those two columns based on the contents of that expression. From what I can glean from the docs, it seems you can only use an expression if the identical expression is used in the SELECT statement, but a column alias seems the more simple approach in that case.

[yukondude]

Here's the explanation from the SQLPlus User's Guide and Reference v10.2: "You can use an expression involving one or more table columns or an alias assigned to a report column in a SQL SELECT or SQLPlus COLUMN command. If you use an expression in a BREAK command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a BREAK command to refer to the expression in the SELECT command." (download.oracle.com/docs/cd/B19306_01/server.102/b14357/…)