SQL help

Database
Enthusiast

SQL help

I have table sales and store_id details

sales_id sales_percentage sales_area

1 10 BG

2 20 ch

3 30 as

store id store name

2 ed

3 tr

6 ns

i need output like below

sales_id sales_percentage sales_area store id

1 10 BG 2 

1 10 BG 3

1 10 BG 6

2 20 ch 2

2 20 ch 3

2 20 ch 6

3 30 as 2

3 30 as 3

3 30 as 6

i have used cross join to get this result

SELECT sales_id sales_percentage sales_area store id from sales

CROSS JOIN store_id

Is there any other way(other than cross) to acheive this.  The sales data has 150 crore recods, hence it is causing performance issue. please help

5 REPLIES
Enthusiast

Re: SQL help

Did you try to use an inner join instead of Cross join?

Enthusiast

Re: SQL help

there is no common id between sales table and store-id table, inorder to bring each sale id from different stores i hav to put cross join. I cannot use inner join as only matching records will come n there is no common id between sales and store tables. Is there any other way to get cross join result
Enthusiast

Re: SQL help

there is no common id between sales table and store-id table, inorder to bring each sale id from different stores i hav to put cross join. I cannot use inner join as only matching records will come n there is no common id between sales and store tables. Is there any other way to get cross join result
N/A

Re: SQL help

Hi,

If you want to achieve this using only a single SQL query, i dont think its possible unless you used a cross join.

However if performance & the output is more important irrespective of the approach, then you can use the below procedure which will generate the result in a final Target Table.

REPLACE PROCEDURE CROSS_JOIN_ALTENATIVE ()

BEGIN

--|====================================================================

--|         Declare  Variables

--|====================================================================

DECLARE V_STORE_ID INTEGER;

--|====================================================================

--|        Declare Exception Handler ( Continue )

--|====================================================================

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

    END;

    DROP TABLE VT_SALES;

END;

--|====================================================================

--|        Beginning of Procedure

--|====================================================================

      /*Creation of final target table*/

      /*Please Create VT once for procedure compilation*/

  CREATE VOLATILE TABLE VT_SALES

  ( sales_id INTEGER,

    sales_percentage INTEGER,

    sales_area VARCHAR(3),

    store_id INTEGER

  ) ON COMMIT PRESERVE ROWS;

    /*For Loop to get the Cross Join Result By Breaking the SQL into equivalent INSERTs with Distinct store_id*/

FOR ST AS Store_IDs CURSOR FOR

  SELECT DISTINCT  store_id

  FROM store_details

  ORDER BY store_id

DO

  SET V_STORE_ID = ST.store_id;

  /*Final Result Table */

       INSERT INTO VT_SALES

       ( sales_id,

    sales_percentage,

    sales_area ,

    store_id

  )

  SELECT

    sales_id,

    sales_percentage,

    sales_area ,

    :V_STORE_ID

    FROM sales;

END FOR;

--|====================================================================

--|        End of Procedure

--|====================================================================

END;

Do let me know, did it help in performance.

Enthusiast

Re: SQL help

Chirag Sure, Thank you.