Tutorial :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

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:2

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".


Solution:3

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/'  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »