Multiply an alias with a field in SQL

Question

Multiply an alias with a field in SQL

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I have this query and I need to multiply SI_Dif * m.SI_Costo_Promedio but it tells me that SI_Dif is not valid.

SELECT t.SI_Articulo,
m.SI_Descripcion, 
t.SI_UM, 
(CASE WHEN c.SI_OV IS NOT NULL THEN '*' ELSE NULL END) AS SI_OV, 
t.SI_Ubicacion, 
t.SI_Existencia, 
c.SI_Cantidad, 
c.SI_Cantidad - t.SI_Existencia AS SI_Dif ,
SI_Dif * m.SI_Costo_Promedio AS SI_Dif2
FROM SI_Inventario_Teorico_QAD t
INNER JOIN SI_Maestro_Ref_QAD m ON m.SI_Num_Articulo = t.SI_Articulo
LEFT JOIN SI_Conteo c ON c.SI_Num_Articulo = t.SI_Articulo

sql sql-server

asked by Eduard 23.06.2017 в 18:55

source

2 answers

1

Use the original fields for the calculation:

...(c.SI_Cantidad - t.SI_Existencias)*m.SI_Costo_Promedio AS SI_Dif2...

The other field does not exist until the query is made.

answered by 23.06.2017 в 19:01

Error capturing value of a text file in java Print array in Java

score 3 · Accepted Answer

In that SELECT SI_Dif does not exist.

You have to do

(c.SI_Cantidad - t.SI_Existencia) * m.SI_Costo_Promedio AS SI_Dif2

Asi:

SELECT t.SI_Articulo,
m.SI_Descripcion, 
t.SI_UM, 
(CASE WHEN c.SI_OV IS NOT NULL THEN '*' ELSE NULL END) AS SI_OV, 
t.SI_Ubicacion, 
t.SI_Existencia, 
c.SI_Cantidad, 
c.SI_Cantidad - t.SI_Existencia AS SI_Dif ,
(c.SI_Cantidad - t.SI_Existencia) * m.SI_Costo_Promedio AS SI_Dif2
FROM SI_Inventario_Teorico_QAD t
INNER JOIN SI_Maestro_Ref_QAD m ON m.SI_Num_Articulo = t.SI_Articulo
LEFT JOIN SI_Conteo c ON c.SI_Num_Articulo = t.SI_Articulo