是否可将视图t_sdet_part与t_sdet_part_all合并为单一视图？

Alright, let's tackle merging these two views into a single, self-contained view. The core idea is to combine the row-generation logic from t_sdet_part directly with the additional field lookup from t_sdet_part_all, so we don't have to rely on two separate views anymore. Here's how you can do it:

CREATE VIEW t_sdet_part_combined AS
WITH v AS (
    SELECT * FROM table_mv
)
SELECT DISTINCT
    d.s_date,
    ig.part_no,
    ig.i_group,
    l.s_level,
    ig.p_category,
    COALESCE(v_qty.qty_ordered, 0) AS qty_ordered,
    v_details.bucket,
    v_details.relief_amt,
    v_details.extreme_amt,
    v_details.curr_mth_note,
    v_details.carryover_note
FROM (SELECT DISTINCT s_date FROM v) d
CROSS JOIN (SELECT DISTINCT part_no, i_group, p_category FROM v) ig
CROSS JOIN (
    SELECT '80' AS s_level FROM DUAL 
    UNION ALL SELECT '81' FROM DUAL 
    UNION ALL SELECT '95' FROM DUAL 
    UNION ALL SELECT '101' FROM DUAL 
    UNION ALL SELECT '100' FROM DUAL 
    UNION ALL SELECT 'Late' FROM DUAL
) l
-- Left join to fetch qty_ordered (matches original t_sdet_part logic)
LEFT JOIN v v_qty 
    ON v_qty.s_date = d.s_date 
    AND v_qty.part_no = ig.part_no 
    AND v_qty.i_group = ig.i_group 
    AND v_qty.s_level = l.s_level
-- Left join to fetch additional detail fields (matches original t_sdet_part_all logic)
LEFT JOIN v v_details 
    ON v_details.s_date = d.s_date 
    AND v_details.part_no = ig.part_no
ORDER BY 
    s_date, 
    part_no, 
    i_group, 
    DECODE(s_level, '80', 1, '81', 2, '95', 3, '101', 4, '100', 5, 'Late', 6);

A quick breakdown of the changes: #

• We keep the initial CTE v to reference table_mv once, keeping the code clean and avoiding redundant calls to the source view.
• The cross-joins for dates, part metadata, and s_level values are preserved exactly as they were in t_sdet_part—this is what generates the full set of rows that might be missing from table_mv.
• We split the left joins into two aliased ones (v_qty and v_details) to clearly separate the logic: one for fetching qty_ordered (which requires matching s_level and i_group) and another for pulling in the extra detail fields (which only needs s_date and part_no matches). This mirrors your original two-view workflow but consolidates it into one step.
• The DISTINCT keyword is retained because the detail join might return multiple rows if table_mv has duplicate entries for the same s_date and part_no, which would otherwise duplicate our generated rows.
• The ordering logic from your original views is kept to ensure the output matches what you're already expecting.

内容的提问来源于stack exchange，提问作者KassieB