CODEWITHSUNDEEP
sqlt-sql
Matías Hernán García
Matías Hernán García

Reputation: 241

Function always returns null even with ISNULL

I have the following function:

CREATE FUNCTION HARDCODERS.getKilosDisponiblesViaje(@Viaje int)

RETURNS TABLE
AS
RETURN 

(
select Micro.KG_Disponibles - SUM(Encomienda.Cant_KG) as KGDisponibles
from HARDCODERS.Micro, HARDCODERS.Viaje, HARDCODERS.Encomienda, HARDCODERS.PasajeEncomienda
where Viaje.Cod_Viaje = @Viaje AND Viaje.Patente = Micro.Patente AND PasajeEncomienda.Cod_Viaje = @Viaje
    AND PasajeEncomienda.Cod_PasajeEncomienda = Encomienda.Cod_PasajeEncomienda
group by Micro.KG_Disponibles,Micro.Patente
)

GO

It works fine if I have rows in Encomienda. However, if I don't the function will always return null.

I added an ISNULL to the SUM, also tried inside of it and it keeps returning null if there are no rows in Encomienda.

Micro.KG_Disponibles always has a value.

Upvotes: 0

Views: 160

Answers (1)

Abe Miessler
Abe Miessler

Reputation: 85056

First off stop using implicit joins. Once you have done that you can use a LEFT JOIN which will probably solve your problem.

Upvotes: 1

Related Questions