• This should work as long as you don't have multiple rows being returned from that subquery

    SELECT dbo.nomfil.nomcode ,

    ISNULL(calc.val, 0) AS ytdper ,

    calc.costcentre

    FROM dbo.nomfil

    LEFT OUTER JOIN ( SELECT CASE WHEN nomfil_1.type IN ( 3, 4 )

    THEN nomtrn.nomcode

    ELSE ( SELECT nomcode

    FROM ctlfil

    WHERE recid = 24

    )

    END AS nomcode ,

    SUM(dbo.nomtrn.val * ( 1 - 2 * dbo.nomtrn.crddbt )) AS val ,

    dbo.nomtrn.costcentre

    FROM dbo.nomtrn

    INNER JOIN dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode

    WHERE ( dbo.nomtrn.period < ( SELECT

    glpernum / 100 * 100 AS Expr1

    FROM

    dbo.perfil

    ) )

    GROUP BY CASE WHEN nomfil_1.type IN ( 3, 4 )

    THEN nomtrn.nomcode

    ELSE ( SELECT nomcode

    FROM ctlfil

    WHERE recid = 24

    )

    END ,

    dbo.nomtrn.costcentre

    ) AS calc ON calc.nomcode = dbo.nomfil.nomcode

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass