sql-server - 如何在 DB2 更新语句中有内部联接和 case 表达式？

Question

我有以下 MSSQL 更新语句，其中包含内部联接和更新语句中的 case 表达式是否可以将更新语句转换为 DB2 更新状态。

UPDATE LIBNAME1.OPTR_POS_FIX
SET VAL_TYPE = @VAL_TYPE
,PORT_SNAME = @PORT_SNAME
,ISIN_NO = @ISIN_NO
,SEC_SNAME = @SEC_SNAME
,SEC_CCY_ABBR = @SEC_CCY_ABBR
,BASE_CCY = @BASE_CCY
,TRX_BCCY_EX_RATE = @TRX_BCCY_EX_RATE
,QUANTITY = @QUANTITY
,MKT_PRICE = @MKT_PRICE
,AVG_COST = @AVG_COST
,MVAL_AMT_SC = ROUND(@QUANTITY * @MKT_PRICE / (
        CASE 
            WHEN FDBVAL.VLGTI = 100
                THEN 100
            ELSE 1
            END
        ), 3)
,MVAL_AMT_BC = ROUND(@QUANTITY * @MKT_PRICE / (
        CASE 
            WHEN FDBVAL.VLGTI = 100
                THEN 100
            ELSE 1
            END
        ) / @TRX_BCCY_EX_RATE, 3)
,AVG_BVAL_SC = @AVG_BVAL_SC
,AVG_BVAL_BC = @AVG_BVAL_BC
,INT_AMT_SC = @INT_AMT_SC
,INT_AMT_BC = @INT_AMT_BC
FROM LIBNAME1.OPTR_POS_FIX
INNER JOIN LIBNAME2.FDBVAL ON OPTR_POS_FIX.SEC_CODE = FDBVAL.VLVALR
WHERE (OPTR_POS_FIX.VALN_DATE = @VALN_DATE)
AND (OPTR_POS_FIX.PORT_CODE = @PORT_CODE)
AND (OPTR_POS_FIX.SEC_CODE = @SEC_CODE)

我很感激任何帮助。

标签： sql-serverdb2

Answer 1

可能是这样的假设，@var是一些应用程序参数：

MERGE INTO LIBNAME1.OPTR_POS_FIX O
USING LIBNAME2.FDBVAL F ON O.SEC_CODE = F.VLVALR
AND (O.VALN_DATE = @VALN_DATE)
AND (O.PORT_CODE = @PORT_CODE)
AND (O.SEC_CODE = @SEC_CODE)

WHEN MATCHED THEN UPDATE SET 
  VAL_TYPE = @VAL_TYPE
, ...
, MVAL_AMT_SC = ROUND(@QUANTITY * @MKT_PRICE / (
        CASE 
            WHEN F.VLGTI = 100
                THEN 100
            ELSE 1
            END
        ), 3)
, ...
;