I'm Making a Query to get the Payments of a Invoice. But since i can have, multiple cards, Accounts Receivable and Money (cash and change). is it possible to make this work without using function, Unions and subqueries? all tables have 300k records, and i want to make a view for ease of use/maintain. however when i use union on views perfromance drops drastically

THE FOLLOWING EXEMPLE

Invoice

Invoice -> entry

Accounts Receivable

Card

Cash

Query

SELECT NAF.INVOICE_ENTRY, ADRE.ID_AR, ACAR.ID_CARD, ADIN.ID_CASH
FROM INVOICE N
INNER JOIN INVOICE_ENTRY NAF
    ON NAF.ID_INVOICE = N.ID_INVOICE
LEFT JOIN AR_ENTRY ADRE
    ON ADRE.ID_ENTRY = NAF.ID_ENTRY
LEFT JOIN CARD_ENTRY ACAR
    ON ACAR.ID_ENTRY = NAF.ID_ENTRY
LEFT JOIN CASH_ENTRY ADIN
    ON ADIN.ID_ENTRY = NAF.ID_ENTRY
WHERE N.ID_INVOICE = :ID_INVOICE 

Result

Expected Result

Conclusion: The desired Result can't be achieved by a simple query, the use of Unions is necessary.

Note: My question was to try a workaround for performance issues. However it would be a diferent question.

CodePudding user response：

You can use UNION ALL on the accounts_receivable, cash and card tables and then join the result to the other tables:

SELECT u.*
FROM   invoice i
       INNER JOIN entry e
       ON (i.invoice = e.invoice)
       INNER JOIN (
         SELECT id_entry AS acerfin, id_ar AS idad, NULL AS idcar, NULL AS iddin
         FROM   accounts_receivable
         UNION ALL
         SELECT id_entry, NULL, id_card, NULL
         FROM   card
         UNION ALL
         SELECT id_entry, NULL, NULL, id_cash
         FROM   cash
       ) u
       ON (e.id_entry = u.acerfin)
WHERE  i.invoice = 113806

Which, for the sample data:

CREATE TABLE invoice (INVOICE) AS
SELECT 113806 FROM DUAL;

CREATE TABLE entry (INVOICE, ID_ENTRY) AS
SELECT 113806, 387473 FROM DUAL;

CREATE TABLE Accounts_Receivable (ID_ENTRY, ID_AR, DUE_TO) AS
SELECT 387473, 31014, DATE '2022-05-15' FROM DUAL UNION ALL
SELECT 387473, 31015, DATE '2022-06-15' FROM DUAL;

CREATE TABLE Card (ID_ENTRY, ID_CARD, DUE_TO) AS
SELECT 387473, 407214, DATE '2022-05-01' FROM DUAL;

CREATE TABLE Cash (ID_ENTRY, ID_CASH, VALUE) AS
SELECT 387473, 407214, 10 FROM DUAL UNION ALL
SELECT 387473, 407215, -5 FROM DUAL;

Outputs:

ACERFIN	IDAD	IDCAR	IDDIN
387473	31014	null	null
387473	31015	null	null
387473	null	407214	null
387473	null	null	407214
387473	null	null	407215

db<>fiddle here