How to Create a Cross JOIN without using MV or Incorta SQL

[Maran_Rengasamy]

Hi All,

As per the company policy, we are not supposed to use Meterialized View(except Bridge table) and Incorta SQL for any data mapping logic.  

I have two tables INVOICE_PAYMENTS and INVOICE_DISTRIBUTIONS. The INVOICE_ID is the Join Key for these two tables. But the INVOICE_ID is not a unique value column. Both tables has multiple INVOICE_ID’s.

Us per the requirement, we need show all the records from both tables. So how to do the Cross Join for these two tables without using any SQL logic/concept.

I can achieve this requirement using Meterialized View, Incorta SQL or Business View(SQL) but we don't want to use these concepts.

Can anyone help me on this?

Below is sample. 

INVOICE_DISTRIBUTIONS
INVOICE_DISTRIBUTIONS_ID	INVOICE_ID
10	1
11	1
12	1
13	2
14	3
15	3

INVOICE_PAYMENTS
INVOICE_PAYMENTS_ID	INVOICE_ID
1	1
2	1
3	2
4	2
5	3
6	3

Required Output
INVOICE_DISTRIBUTIONS_ID	INVOICE_PAYMENTS_ID	INVOICE_ID
10	1	1
10	2	1
11	1	1
11	2	1
12	1	1
12	2	1
13	3	2
13	4	2
14	5	3
15	6	3
14	5	3
15	6	3

[JoeM · Accepted solution]

@Maran_Rengasamy - Thank you for the detail explanation. A cross-join can not be achieved without a MV or a SQL View. 

[Maran_Rengasamy]

Hi Joe,

Thanks for the confirmation.