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

Maran_Rengasamy — Tue, 13 Aug 2024 18:07:20 GMT

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

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

JoeM — Thu, 15 Aug 2024 18:48:24 GMT

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

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

Maran_Rengasamy — Fri, 16 Aug 2024 17:29:58 GMT

Hi Joe,

Thanks for the confirmation.

topic Re: How to Create a Cross JOIN without using MV or Incorta SQL in Data & Schema Discussions

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

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

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