Multiplication in sql, error by INNER JOIN

1

I have this Query

SELECT
    SI_Articulo = COALESCE(t.SI_Articulo, c.SI_Num_Articulo),
    SI_Ubicacion = COALESCE(t.SI_Ubicacion, c.SI_Ubicacion),
    SI_OV = COALESCE(c.SI_OV,''),
    SI_Ubicacion_Fisica = COALESCE(c.SI_Ubicacion_Fisica,''),
    SI_Existencia = COALESCE(t.SI_Existencia, 0),
    SI_Cantidad = COALESCE(c.SI_Cantidad, 0),
    SI_Dif =  COALESCE(c.SI_Cantidad, 0) - COALESCE(t.SI_Existencia, 0),
    SI_Dif_Dinero = COALESCE(c.SI_Cantidad,0) - COALESCE(t.SI_Existencia,0) * COALESCE(m.SI_Costo_Promedio,0) 
    FROM
    SI_Inventario_Teorico_QAD t
    FULL JOIN SI_Conteo c
    ON t.SI_Articulo = c.SI_Num_Articulo
    AND t.SI_Ubicacion = c.SI_Ubicacion
    FULL JOIN SI_Maestro_Ref_QAD m 
    ON  t.SI_Articulo = m.SI_Num_Articulo
    WHERE c.SI_Num_Articulo = 200116 OR t.SI_Articulo = 200116

which brings me the next result

My problem is that for example the locations DD5131 and EE2102 do not exist in the table SI_Inventario_Teorico_QAD t therefore when I do the next operation

SI_Dif_Dinero = COALESCE(c.SI_Cantidad,0) - COALESCE(t.SI_Existencia,0) * COALESCE(m.SI_Costo_Promedio,0)

I do not multiply it by the value that m.SI_Costo_Promedio brings since it brings me a NULL value

so how can I tell you that if the location is new, multiply it by m.SI_Costo_Promedio

    
asked by Eduard 07.07.2017 в 21:12
source

2 answers

1

Use the IsNull () function in the following way:

IsNull(columna1 * columna2, 0);

This indicates that if the multiplication of both columns results in a Null the result is 0.

I hope your answer will help, greetings.

    
answered by 07.07.2017 в 22:59
0

Try changing the comparison of the last JOIN in the following way

ON  COALESCE(t.SI_Articulo, c.SI_Num_Articulo) = m.SI_Num_Articulo
    
answered by 07.07.2017 в 23:09