Say I have a table of invoice transactions and one of the fields is invoice_number. Each invoice number needs to be unique for its vendor, so vendor A can have invoice numbers ABC001 and ABC002 and vendor B can also have invoice numbers ABC001 and ABC002. Included in this table are erroneous invoice transactions that have some kind of suffix appended to the end of the invoice number. It could be ABC001-1 or ABC001/1 or ABC001A or any other possible permutation. Here's my question: can I use a window function to identify the "real" invoices that have these suffix'd transactions? I'm envisioning some kind of wildcard in the PARTITION BY clause, but I can't figure out how that'd work:
SELECT
...
,COUNT(invoice_number) OVER (PARTITION BY 'invoice_number%') AS count
FROM invoices_table
In my brain, the end result would look something like this, where the invoices with counts greater than 1 are the ones that have the additional rows with the suffixes:
| vendor | invoice_number | count |
|---|---|---|
| A | ABC001 | 2 |
| A | ABC001-1 | 1 |
| A | ABC002 | 1 |
| A | ABC003 | 2 |
| A | ABC003A | 1 |
| B | DEF001 | 1 |
| B | DEF002 | 1 |
Edit: I added a second vendor to the example table and I'm working in Oracle EBS.