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