sqloracle-databasesubquerycommon-table-expressioninline-view

First query data is used in second query


I have a query get_product:

select A.product_id,
A.name, A.description, A.type_id,
B.series_name
product_data A
inner join
series B
on A.series_raw_id = B.series_raw_id 
where A.product_id = 503061
and A.registration_type_id = 4
order by B.series_name 

and second query

select B.series_name,
A.TEMPACC_STATUS 
FROM 
ACCESS_FACT A
inner join 
**get_product** B
on A.TEMPACC_PRODUCT_ID = B.product_id
where A.TEMPACC_DATE_ID between 6717 and 6808 
and A.reason_id_total = 0
group by Series_name, 
STATUS

In the second query we use data from first query (get_product is the first query). How do I get that table here?


Solution

  • You could use the WITH clause.

    For example,

    WITH get_product AS
      (SELECT A.product_id,
        A.name,
        A.description,
        A.type_id,
        B.series_name product_data A
      INNER JOIN series B
      ON A.series_raw_id         = B.series_raw_id
      WHERE A.product_id         = 503061
      AND A.registration_type_id = 4
      ORDER BY B.series_name
      )
    SELECT B.series_name,
      A.TEMPACC_STATUS
    FROM ACCESS_FACT A
    INNER JOIN get_product B
    ON A.TEMPACC_PRODUCT_ID = B.product_id
    WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
    AND A.reason_id_total = 0
    GROUP BY Series_name,
      STATUS;
    

    Or, you could use an INLINE VIEW

    SELECT B.series_name,
      A.TEMPACC_STATUS
    FROM ACCESS_FACT A
    INNER JOIN
      (SELECT A.product_id,
        A.name,
        A.description,
        A.type_id,
        B.series_name product_data A
      INNER JOIN series B
      ON A.series_raw_id         = B.series_raw_id
      WHERE A.product_id         = 503061
      AND A.registration_type_id = 4
      ORDER BY B.series_name
      ) B ON A.TEMPACC_PRODUCT_ID = B.product_id
    WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
    AND A.reason_id_total = 0
    GROUP BY Series_name,
      STATUS;