demo-Examples-v4-0002-View_with_join_Examples.sql.txt

-- Add a material table with join and create the view "materials2".
CREATE TABLE "materials2" ("material_id" varchar(20),"material" varchar(30));

CREATE OR REPLACE VIEW v_invoices2 AS
SELECT 
    invoice_id,
    document_date,
    material_code,
	materials2.material,
    quantity,
    amount,
    approval_status,
    data_control_scope,
    sales_org,
    valuation_order_flag,
    created_at
FROM invoices
LEFT JOIN materials2 ON invoices.material_code = materials2.material_id



-- Option 2: Uses a view and uses GROUP BY ROLLUP.
EXPLAIN ANALYZE
SELECT
    COALESCE(v_invoices.document_date::TEXT, 'Total') AS "document_date",
    COALESCE(SUM(v_invoices.amount), 0) AS "amount"
FROM v_invoices
WHERE
    (v_invoices.document_date BETWEEN '2024-06-01' AND '2024-12-31' 
     OR v_invoices.document_date BETWEEN '2025-01-01' AND '2025-12-09')
    AND v_invoices.data_control_scope = 'DD_OG'
    AND v_invoices.valuation_order_flag = '1'
    AND v_invoices.approval_status IN ('y')
    AND v_invoices.sales_org IN ('DF01', 'DG01')
GROUP BY ROLLUP (v_invoices.document_date);


-- Option 4: The view with join is used and employs GROUP BY ROLLUP.
EXPLAIN ANALYZE
SELECT
    COALESCE(v_invoices2.document_date::TEXT, 'Total') AS "document_date",
    COALESCE(SUM(v_invoices2.amount), 0) AS "amount"
FROM v_invoices2
WHERE
    (v_invoices2.document_date BETWEEN '2024-06-01' AND '2024-12-31' 
     OR v_invoices2.document_date BETWEEN '2025-01-01' AND '2025-12-09')
    AND v_invoices2.data_control_scope = 'DD_OG'
    AND v_invoices2.valuation_order_flag = '1'
    AND v_invoices2.approval_status IN ('y')
    AND v_invoices2.sales_org IN ('DF01', 'DG01')
GROUP BY ROLLUP (v_invoices2.document_date);