SQL Server查询迁移Snowflake时的子查询报错问题

问题场景 #

迁移SQL Server查询至Snowflake时触发Unsupported subquery type cannot be evaluated错误：

• 子查询硬编码'SAL3048'时查询正常运行
• 尝试引用主查询PORTCALLS.rhreise字段替代硬编码值时报错
• 生产环境需移除WHERE子句硬编码，实现动态获取主查询字段值

解决方案 #

Snowflake对嵌套标量子查询的关联支持有限，需将原标量子查询重构为预计算CTE+JOIN的方式，提前算出每个航次的第1、2个港口信息，再与主查询关联：

/****  VESSEL POSITION REPORT *****/
WITH VoyagePortPreCalc AS (
    SELECT 
        t1.rhreise,
        MAX(CASE WHEN t1.rhseqno = 1 THEN t1.rhetseta END) AS FirstPortDate,
        MAX(CASE WHEN t1.rhseqno = 1 THEN t1.rhhafen END) AS FirstPort,
        MAX(CASE WHEN t1.rhseqno = 2 THEN t1.rhetseta END) AS SecondPortDate
    FROM (
        -- 获取每个船舶+公司的最新版本记录
        SELECT rhrmanr, rhvessel, MAX(rhstatper) AS MaxCreated
        FROM edb.Softship.reise_hafen
        GROUP BY rhrmanr, rhvessel
    ) t2
    JOIN edb.Softship.reise_hafen t1
        ON t2.rhrmanr = t1.rhrmanr
        AND t2.rhvessel = t1.rhvessel
        AND t2.MaxCreated = t1.rhstatper
    WHERE t1.rhseqno IN (1,2) -- 只保留第1、2个港口
    GROUP BY t1.rhreise
)
SELECT 
    PORTCALLS.rhvessel AS VesselCode,
    VESSELS.vsname AS VesselName,
    PORTCALLS.rhservice AS Service,
    PORTCALLS.rhreise AS Voyage,
    PORTCALLS.rhhafen AS PortCode,
    PORTLKUP.lcname AS PortName,
    BERTHCALLS.rbberthcode AS TermCode,
    BERTHCALLS.rbberthname AS Terminal,
    VOYAGES.rereisenr2 AS AltVoyNum,
    PORTCALLS.rhcustdecno AS CustVoyNum,
    VOYAGES.republished AS Published,
    PORTCALLS.rhstatus AS PortCallStatus,
    PORTCALLS.rheta AS PortArrival,
    BERTHCALLS.rbbertharrtime AS BerthArrive,
    BERTHCALLS.rbberthstarttime AS BerthStart,
    BERTHCALLS.rbberthendtime AS BerthEnd,
    BERTHCALLS.rbberthsailtime AS BerthDepart,
    PORTCALLS.rhetseta AS PortDepart,
    PORTCALLS.rhseqno AS PortSequence,
    PORTCALLS.rhstatper AS StatPeriod,
    PORTCALLS.reise_hafen_id,
    PORTCALLS.rhcallid AS PortCallID,
    -- 直接从预计算CTE获取字段
    vppc.FirstPortDate,
    vppc.FirstPort,
    vppc.SecondPortDate
FROM edb.softship.reise_hafen AS PORTCALLS
JOIN edb.softship.lokation AS PORTLKUP
    ON PORTLKUP.firma = 'CRMLS'
    AND PORTLKUP.lccode = PORTCALLS.rhhafen
JOIN edb.softship.schiff AS VESSELS
    ON VESSELS.firma = 'CRMLS'
    AND VESSELS.vscode = PORTCALLS.rhvessel
JOIN edb.softship.reise AS VOYAGES
    ON VOYAGES.reschiff = PORTCALLS.rhvessel
    AND voyages.rereisenr = PORTCALLS.rhreise
    AND voyages.resource = 'Voyces'
LEFT JOIN edb.softship.reise_berth AS BERTHCALLS
    ON BERTHCALLS.rbvessel = PORTCALLS.rhvessel
    AND BERTHCALLS.rbvoyage = PORTCALLS.rhreise
    AND BERTHCALLS.rbhafen = PORTCALLS.rhhafen
    AND BERTHCALLS.rbseqno = PORTCALLS.rhseqno
-- 关联预计算CTE，动态匹配当前航次
JOIN VoyagePortPreCalc vppc
    ON vppc.rhreise = PORTCALLS.rhreise
WHERE PORTCALLS.firma = 'CRMLS'
-- 移除硬编码的航次条件，生产环境可直接运行
-- AND PORTCALLS.rhreise = 'SAL3048'

关键优化点 #

• 用VoyagePortPreCalc CTE一次性计算所有航次的第1、2个港口信息，避免重复子查询
• 通过JOIN替代嵌套标量子查询，解决Snowflake的子查询兼容性问题
• 保留原查询的业务逻辑，同时实现动态关联主查询的rhreise字段

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