CODEWITHSUNDEEP
sqloracle-database
IngridSkard
IngridSkard

Reputation: 37

Oracle SQL: Using stored procedures to fetch data connected by a field of comma-delimited id's

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!

Upvotes: 1

Views: 204

Answers (1)

ramana_k
ramana_k

Reputation: 1933

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

Upvotes: 1

Related Questions