CTE Example:
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Query to find out the Model present in the ItmSup1 file that are present in any 2 of the 3 files (ItmStr1, ItmStr2, ItmStr3)
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Query to find out the Model present in the ItmSup1 file that are present in any 2 of the 3 files (ItmStr1, ItmStr2, ItmStr3)
with tbl1 (SKU) as
(select model from devlib/ItmStr1
union all
select model from devlib/ItmStr2
union all
select model from devlib/ItmStr3
), tbl2 (model) as (
select SKU
from tbl1
where Sku in (select model from
devlib/ItmSup1)
group by Sku
having count(*) = 2
)
select count(*) from tbl2
Comments
Post a Comment