Rajarshi Bhadra Rajarshi Bhadra - 7 months ago 11
SQL Question

Running query without intermediate data creation in sql

My query looks like this

CREATE TABLE work_pr.op1 AS

SELECT DISTINCT level1_idnt
,org_sales_price
FROM md1.item_lv1_org_price_m
WHERE fr_cntry_cde = '01';

CREATE TABLE work_pr.op2 AS

SELECT level1_idnt
,org_sales_price
FROM work_pr.op1
WHERE org_sales_price IS NOT NULL;

CREATE TABLE work_pr.final_op AS

SELECT level1_idnt
,avg(org_sales_price) AS op
FROM work_pr.op2
GROUP BY level1_idnt;


I want the steps to be in same sequence except that I dont want to create work_pr.op1 and the work_pr.op2 tables. How can I shorten this process in sql. I am very new to sql and any help will be greatly appreciated

Answer

You can just use nested selects in order to create a single table, the last one:

CREATE TABLE work_pr.final_op AS

SELECT level1_idnt
    ,avg(org_sales_price) AS op
FROM (
    SELECT level1_idnt
        ,org_sales_price
    FROM (
        SELECT DISTINCT level1_idnt
            ,org_sales_price
        FROM md1.item_lv1_org_price_m
        WHERE fr_cntry_cde = '01'
        ) result_set_1
    WHERE org_sales_price IS NOT NULL
    ) result_set_2
GROUP BY level1_idnt;

As @GordonLinoff mentioned, the SELECT can be simplified and you can use the one in his answer.