The core issue here is that aggregate functions like MAX() can't be used directly in a CASE expression without wrapping them in a valid scalar subquery (one that returns a single value per row). Additionally, your original query had a syntax error in the join order—each INNER JOIN needs its ON clause immediately after the table it's joining. Below are tailored solutions based on your needs:

Solution 1: Global Maximum (Across All SECS Rows) #

If you want the highest INTPAYAMT_8 value from the entire TBL_FR2052A_SECS_HIST table:

UPDATE TBL_FR2052A_TPOS_HIST_SPLIT 
SET PRINCIPAL = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
    END, 
    INTEREST = CASE 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * (SELECT MAX(INTPAYAMT_8) FROM TBL_FR2052A_SECS_HIST) 
    END, 
    INTPAYAMTQTY = CASE 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END, 
    REPORTABLE_AMOUNT = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * (SELECT MAX(INTPAYAMT_8) FROM TBL_FR2052A_SECS_HIST) 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END 
FROM TBL_FR2052A_RAW_DATA_HIST AS RawData 
INNER JOIN TBL_FR2052A_TPOS_HIST_SPLIT AS TPOS 
    ON RawData.SourceID_Revised = TPOS.SourceID_Revised AND RawData.AsofDate = TPOS.ASOFDATE
INNER JOIN TBL_FR2052A_SECS_HIST AS SECS 
    ON SECS.SECID = TPOS.SECID AND SECS.ASOFDATE = TPOS.ASOFDATE

Solution 2: Maximum Per SECID & ASOFDATE (Matching Current Row) #

If you need the highest INTPAYAMT_8 for the same SECID and ASOFDATE as the current TPOS row, use a correlated subquery:

UPDATE TBL_FR2052A_TPOS_HIST_SPLIT 
SET PRINCIPAL = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
    END, 
    INTEREST = CASE 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * (
            SELECT MAX(INTPAYAMT_8) 
            FROM TBL_FR2052A_SECS_HIST 
            WHERE SECID = TPOS.SECID AND ASOFDATE = TPOS.ASOFDATE
        ) 
    END, 
    INTPAYAMTQTY = CASE 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END, 
    REPORTABLE_AMOUNT = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * (
            SELECT MAX(INTPAYAMT_8) 
            FROM TBL_FR2052A_SECS_HIST 
            WHERE SECID = TPOS.SECID AND ASOFDATE = TPOS.ASOFDATE
        ) 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END 
FROM TBL_FR2052A_RAW_DATA_HIST AS RawData 
INNER JOIN TBL_FR2052A_TPOS_HIST_SPLIT AS TPOS 
    ON RawData.SourceID_Revised = TPOS.SourceID_Revised AND RawData.AsofDate = TPOS.ASOFDATE
INNER JOIN TBL_FR2052A_SECS_HIST AS SECS 
    ON SECS.SECID = TPOS.SECID AND SECS.ASOFDATE = TPOS.ASOFDATE

Solution 3: Efficient Precomputation with CTE (Recommended) #

Since you use the MAX value in two places, precompute it once with a CTE to avoid redundant calculations and improve readability:

WITH SecsMaxIntPay AS (
    SELECT 
        SECID, 
        ASOFDATE, 
        MAX(INTPAYAMT_8) AS MaxIntPayAmt
    FROM TBL_FR2052A_SECS_HIST
    GROUP BY SECID, ASOFDATE
)
UPDATE TBL_FR2052A_TPOS_HIST_SPLIT 
SET PRINCIPAL = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
    END, 
    INTEREST = CASE 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * SecsMax.MaxIntPayAmt 
    END, 
    INTPAYAMTQTY = CASE 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END, 
    REPORTABLE_AMOUNT = CASE 
        WHEN TPOS.PID = 'O.D.7' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.A.1' THEN TPOS.MKTVAL 
        WHEN TPOS.PID = 'I.O.8' THEN TPOS.PRINAMT 
        WHEN TPOS.PID = 'O.O.19' THEN TPOS.QTY * SECS.INTPAYAMT_8 
        WHEN TPOS.PID = 'I.O.6' THEN TPOS.QTY * SecsMax.MaxIntPayAmt 
        WHEN TPOS.PID = 'O.W.16' THEN TPOS.MKTVAL + (TPOS.QTY*SECS.INTPAYAMT_8) 
    END 
FROM TBL_FR2052A_RAW_DATA_HIST AS RawData 
INNER JOIN TBL_FR2052A_TPOS_HIST_SPLIT AS TPOS 
    ON RawData.SourceID_Revised = TPOS.SourceID_Revised AND RawData.AsofDate = TPOS.ASOFDATE
INNER JOIN TBL_FR2052A_SECS_HIST AS SECS 
    ON SECS.SECID = TPOS.SECID AND SECS.ASOFDATE = TPOS.ASOFDATE
INNER JOIN SecsMaxIntPay AS SecsMax 
    ON SecsMax.SECID = TPOS.SECID AND SecsMax.ASOFDATE = TPOS.ASOFDATE

Key Notes: #

• Join Order Fix: I reorganized the joins to pair each INNER JOIN with its ON clause immediately after the joined table—this is required in most SQL dialects to avoid syntax errors.
• Scalar Subqueries: The subqueries used return exactly one value per row, which is mandatory for use in a CASE expression.
• CTE Efficiency: The CTE approach calculates the maximum values once, making the query faster and easier to maintain for large datasets.

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