1

I have been tortured by this issue over the last two weeks and can't spend any more time on it. I would love to hear any ideas from this community, if you have a minute. I'm self taught so I'll have to explain this in lay terms. Thank you for your consideration.

My goal is to fetch all invoices with nonzero balance. This is straightforward enough for regular invoices. The problem arises with payment reminders, because their balance is just the late fee, so in order to fetch their total balance I have to link back to their original invoices through a single field in a connecting table COLLECTIVEINVOICENO.

Example of table INVOICE:

invoiceid     | invoiceno     | invoicetypename     | amount
--------------|---------------|---------------------|--------------------
10008         | 123000        | Payment Reminder    | 5
10005         | 113000        | Payment Reminder    | 5
10001         | 110000        | Invoice             | 35

Example of table COLLECTIVEINVOICENO where 10001 and 10005 are overdue:

invoiceid     | followed_up_in (CHAR)
--------------|----------------------------------------------------------
10005         | 113000, 123000
10001         | 113000

I have found a very bad solution, which works to fetch the ledger of a single customer at a time, but it is so demanding of resources there is no way I can use it on the entire table INVOICE:

select (
  select sum(i.amount) as sum
  from INVOICE i0
  join COLLECTIVEINVOICENO cin0 on cin0.invoiceid = i0.invoiceid
  where instr(cin0.followed_up_in, i.invoiceno) > 0
  ) as original_sum
from INVOICE i

This full table scan is repeated five times in my main select so as to sort various balances into different columns, and for fetching other data from original invoices besides their balance, such as their locations etc.

My original intention was to left-join the subselect onto INVOICE i as a view, but there Oracle doesn't recognize i.invoice in instr(cin0.collectiveinvoiceno, i.invoice) (ORA-00904: "I"."INVOICENO": invalid identifier).

I am now left to looking into cursors etc., which I am currently doing, but I would really like to know if there are any SQL, non-PL ways about it?

Thank you.

Edit: About splitting the comma-separated data into rows ie. changing the data model to facilitate relations: That would certainly help I'm sure, however the data model is packaged with a third-party software so I can't do anything about it. (I would not be surprised if they do change it at some point though, because it manifests with the end user as a major weakness.)

Edit 2: Have tried splitting the comma-separated data into rows using regexp_substr() with level and connect by and join this view to the main table. I doesn't solve the problem because when I regexp_substr() is expensive resources-wise when other tables are joined on, and the query doesn't deliver even after half an hour. Google reveals that this is a common problem with regular expressions and one is advised to try stored procedures instead.

I've changed the title to reflect this and I will be grateful on any input on how to solve this using stored procedures. Anyway, thanks!

IngridSkard
  • 37
  • 6
  • Possible duplicate of [Splitting string into multiple rows in Oracle](http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) – gmiley Nov 04 '15 at 18:31
  • 5
    Storing comma-separated values in a relational column is a path to heartache. Are you sure you don't want to fix the underlying data model problem so that you can let the database work for you rather than having it fight against you? – Justin Cave Nov 04 '15 at 19:23
  • I'm not sure if you've mis-typed your example, or if I'm just not following the datamodel. Per the sample data you've given here, and you're description, it's hard to tell if 10001 and 10005 are independent. What do you expect to see for final balance? Just one row - INVOICE 10001 - with balance of 45? – KevinKirkpatrick Nov 04 '15 at 19:41
  • Thanks for the input. Kevin, they are independent invoices, however 10001 is followed up in 10005, which is again followed up in 10008, making the latter the collective payment reminder of the two former. Their balances are such as 10001=35, 10003=35+5, 10008=35+5+5. – IngridSkard Nov 04 '15 at 20:37
  • To be more precise to your question Kevin: If a payment reminder originates from two invoices of balances 35 and 45, i expect a final balance of 80, which I will add to the balance of the payment reminder, 5, and get the total sum of that payment reminder, 85. If one or both of the original invoices are payment reminders of 5 each, then, in this system, both late-fees are entered as loss automatically when they turn overdue. So the total sum will still be 85. – IngridSkard Nov 04 '15 at 20:50
  • Never, ever store comma separated value. Never. Just don't –  Nov 05 '15 at 18:19

1 Answers1

1

This may help. It splits the comma delimited followed_up_in and flattens the resulting list values into rows of invoice numbers using xmltable before joining with main table.

      select i0.invoiceid, sum(i0.amount)
      from INVOICE i0
      left outer join 
      ( select  invoice_id, trim(COLUMN_VALUE) invoiceno
            FROM COLLECTIVEINVOICENO, 
              xmltable(('"' || REPLACE(followed_up_in, ',', '","') || '"'))
      ) cin0
      on cin0.invoiceno = i0.invoiceno
         and i0.invoiceid = cin0.invoiceid
      group by i0.invoiceid
ramana_k
  • 1,933
  • 2
  • 10
  • 14
  • Thanks Thomas, that looks awesome. I am going to try it tomorrow. – IngridSkard Nov 04 '15 at 20:43
  • I have followed your suggestion Thomas, and Oracle runs it but it runs "forever". When unit testing I find that regexp_substr() seems to cause the hang-up. When I run regexp_substr() from dual however, it works without delay. I Wonder if the developers could have put a restriction on using `level` on their views (I'm working from views), or something in that vein. Thanks anyway. – IngridSkard Nov 05 '15 at 14:55
  • Well, it turns out that `regexp_substr()` can only deliver if nothing else is joined onto the main table, that is both in the view and under the main `select`! – IngridSkard Nov 05 '15 at 15:15
  • 1
    There is an alternative that uses xmltable instead of regexp. I don't know its limitations, it seems to work inside a view. I have no idea about how good its performance would be. That is, just in case if you would like to try. – ramana_k Nov 05 '15 at 17:47
  • It works beautifully. And so much faster! Thank you! – IngridSkard Nov 06 '15 at 13:55