Team,
I have created two WITH temporary tables. Can anyone suggest them how to Join those two tables and call it in Select Query.
For Example
I need to join sum_sales1 and sum_sales2 and calling it in select query.
WITH
sum_sales1 AS
( select /*+ materialize */
sum(quantity) all_sales from sales ),
number_stores AS
( select /*+ materialize */
count(*) nbr_stores from store ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales group by store_name)
SELECT
store_name, store_sales, all_sales / nbr_stores avg_sales
FROM
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores);
WITH
sum_sales2 AS
( select /*+ materialize */
sum(quantity) all_sales from sales ),
number_stores AS
( select /*+ materialize */
count(*) nbr_stores from store ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales group by store_name)
SELECT
store_name, store_sales, all_sales / nbr_stores avg_sales
FROM
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores);
Thanks!
Kiran