I have to make a query similar to
SELECT
CASE WHEN
(SELECT MAX(Fecha) FROM Tabla) > '01/01/1900' THEN (SELECT MAX(Fecha) FROM Tabla)
ELSE '01/01/1900' END
The problem is that the (SELECT MAX(Fecha) FROM Tabla) is running twice. One to evaluate the data and another to show it.
The query (this is just an example) I have inside a function that returns a table and I can not declare variables in them.
Is there any way to avoid this without having to use a scalar function?
On the understanding that your query is an example and only works for a value, in the case of a query of multiple records you could make a subquery.
SELECT Inf.Campo, CASE
WHEN Inf.Fecha > '01/01/1900' THEN Inf.Fecha
ELSE '01/01/1900'
END
FROM (
SELECT CampoAgrupador AS Campo, MAX(Fecha)
FROM dbo.fnDatos()
GROUP BY CampoAgrupador
) AS Inf
The Grouping Field depends on the data that contains the result of your function and what you occupy to group.