将含自定义函数的SQL Server视图迁移至Snowflake遇递归CTE无结果问题

Root Causes of Empty cte_GetEquipmentShiftCalendarID_part_a #

• Missing DateTime Filter: The original SQL Server function cfn_GetEquipmentShiftCalendarID uses both EquipmentID and DateTime to find the active ShiftCalendar, but your CTE didn't filter for ShiftCalendars valid for the target date (begindate ≤ DateTime ≤ enddate).
• Potential Data Type Mismatch: If ShiftCalendarEntityNumber (from Equipment) and entitynumber (from ShiftCalendar) are different data types (e.g., integer vs string), the join between these tables will fail to return matches.
• Incomplete Hierarchy Mapping: While your recursive CTE traverses the equipment hierarchy, it didn't ensure we pick the nearest ancestor with a valid ShiftCalendarEntityNumber, leading to missing mappings.

Corrected Snowflake Code (Focused on First UNION Segment) #

WITH table_equipment AS (
    SELECT 
        id,
        name,
        ParentEquipmentID,
        ShiftCalendarEntityNumber
    FROM DB_BI_DEV.RAW_CPMS_LAG.Equipment
),
table_ShiftCalendar AS (
    SELECT 
        id,
        entitynumber,
        begindate,
        enddate
    FROM DB_BI_DEV.RAW_CPMS_LAG.ShiftCalendar
),
table_shift AS (
    SELECT 
        ID,
        Reference,
        FromDay,
        FromTimeOfDay,
        ShiftCalendarID
    FROM DB_BI_DEV.RAW_CPMS_LAG.shift
),
-- Aggregate scrap registration data (matches original SQL Server temp subquery)
scrap_data AS (
    SELECT 
        CAST(SUM(sreg.ScrapQuantity) AS INT) AS ScrapQuantity,
        sreas.Name AS ScrapReason,
        DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30)::INT, DATE_TRUNC('HOUR', sreg.ScrapTime)) AS DateTime,
        srer.EquipmentID AS EquipmentID
    FROM DB_BI_DEV.RAW_CPMS_LAG.ScrapRegistration sreg
    INNER JOIN DB_BI_DEV.RAW_CPMS_LAG.ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN DB_BI_DEV.RAW_CPMS_LAG.WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN DB_BI_DEV.RAW_CPMS_LAG.SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY 
        DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30)::INT, DATE_TRUNC('HOUR', sreg.ScrapTime)),
        srer.EquipmentID,
        sreas.Name
),
-- Recursive CTE to traverse equipment hierarchy and collect shift calendar entity numbers
equipment_hierarchy AS (
    SELECT 
        ID AS EquipmentID,
        ParentEquipmentID,
        ShiftCalendarEntityNumber
    FROM table_equipment
    
    UNION ALL
    
    SELECT 
        r.EquipmentID, -- Retain original equipment ID
        p.ParentEquipmentID,
        p.ShiftCalendarEntityNumber
    FROM equipment_hierarchy r
    INNER JOIN table_equipment p ON p.ID = r.ParentEquipmentID
    WHERE r.ShiftCalendarEntityNumber IS NULL -- Stop recursion once entity number is found
),
-- Get the nearest valid shift calendar entity number for each equipment
equipment_shift_entity AS (
    SELECT 
        EquipmentID,
        ShiftCalendarEntityNumber
    FROM (
        SELECT 
            EquipmentID,
            ShiftCalendarEntityNumber,
            ROW_NUMBER() OVER (PARTITION BY EquipmentID ORDER BY CASE WHEN ShiftCalendarEntityNumber IS NOT NULL THEN 0 ELSE 1 END) AS rn
        FROM equipment_hierarchy
    )
    WHERE rn = 1 -- Pick first (nearest) entity number in hierarchy
),
-- Map equipment to active shift calendar using DateTime
equipment_shift_calendar AS (
    SELECT 
        sd.EquipmentID,
        sd.DateTime,
        sc.id AS ShiftCalendarID
    FROM scrap_data sd
    INNER JOIN equipment_shift_entity ese ON sd.EquipmentID = ese.EquipmentID
    -- Ensure data types match (adjust casting if needed)
    INNER JOIN table_ShiftCalendar sc ON sc.entitynumber::VARCHAR = ese.ShiftCalendarEntityNumber::VARCHAR
        AND sd.DateTime BETWEEN sc.begindate AND sc.enddate -- Filter for active shift calendar
),
-- Resolve shift ID using DateTime and ShiftCalendarID (adjust logic to match original function)
equipment_shift AS (
    SELECT 
        esc.EquipmentID,
        esc.DateTime,
        s.ID AS ShiftID,
        s.Reference AS Shift,
        s.FromDay,
        s.FromTimeOfDay
    FROM equipment_shift_calendar esc
    INNER JOIN table_shift s ON s.ShiftCalendarID = esc.ShiftCalendarID
        -- Replace with logic from cfn_GetShiftIDFromDateTime
        AND DATE_PART('DAYOFWEEK', esc.DateTime) = s.FromDay
        AND TIME(esc.DateTime) BETWEEN s.FromTimeOfDay AND s.ToTimeOfDay -- Assume Shift has ToTimeOfDay column
)
-- Final select matching original view structure
SELECT 
    e.Name AS ProductionUnit,
    sd.DateTime AS DateTime,
    es.Shift,
    TIME(sd.DateTime) AS Time,
    DATE(sd.DateTime - COALESCE(
        (SELECT MIN(s_first.FromTimeOfDay) FROM table_shift s_first WHERE s_first.FromDay = es.FromDay AND s_first.ShiftCalendarID = esc.ShiftCalendarID),
        TIME('06:00:00')
    )) AS ProductionDate,
    sd.ScrapReason,
    sd.ScrapQuantity,
    'Manually Registered' AS RegistrationType
FROM scrap_data sd
INNER JOIN table_equipment e ON sd.EquipmentID = e.ID
INNER JOIN equipment_shift_calendar esc ON sd.EquipmentID = esc.EquipmentID AND sd.DateTime = esc.DateTime
INNER JOIN equipment_shift es ON sd.EquipmentID = es.EquipmentID AND sd.DateTime = es.DateTime;

Key Fixes Explained #

• DateTime Filter: Added sd.DateTime BETWEEN sc.begindate AND sc.enddate to ensure only active ShiftCalendars are selected for the target date.
• Data Type Alignment: Explicit casting (::VARCHAR) resolves potential type mismatches between entitynumber and ShiftCalendarEntityNumber—adjust the cast type (e.g., ::INT) based on your actual data types.
• Hierarchy Resolution: Used ROW_NUMBER() to pick the nearest ancestor with a valid ShiftCalendarEntityNumber, ensuring accurate equipment-to-calendar mappings.
• Redundant CTE Removal: Simplified the query by removing unnecessary intermediate CTEs.
• Shift ID Logic: Placeholder logic for resolving Shift ID from DateTime and ShiftCalendarID—update this to match the exact logic of cfn_GetShiftIDFromDateTime.

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