SQL UPDATE SUM VALUE NOT NULL

Question

SQL UPDATE SUM VALUE NOT NULL

Navigation

#1 by (0 votes)

0

dataTmp     referencia  nStock
2017-02-18  DC01234567  NULL    
2017-02-18  DC01234567  NULL    
2017-02-18  DC01234567  NULL    
2017-02-19  DC01234567  0   
2017-02-19  DC01234567  0   
2017-02-19  DC01234567  0   
2017-02-20  DC01234567  0
2017-02-20  DC01234567  -450    
2017-02-20  DC01234567  -450    
2017-02-18  RE21341241  NULL    
2017-02-18  RE21341241  NULL    
2017-02-18  RE21341241  NULL    
2017-02-19  RE21341241  1000    
2017-02-19  RE21341241  1000
2017-02-19  RE21341241  1000    
2017-02-20  RE21341241  1000
2017-02-20  RE21341241  -5000   
2017-02-20  RE21341241  -6000

I have this table where I update from a "Stored Procedures" the value of today in all references, Add the Stock in a table where you have the stocks and poe the results In this same table, for example, search the "sum ( stock) from stocks "and places on the respective day

The problem is that when it does not find the value it places NULL, what I want is to put 0 when I can not find the sum ... I already tried to search but I'm a little lost

Stored Procedure:

UPDATE tabela1 SET nStock=(
SELECT  SUM(stock) as TotalStock
FROM Tabelastock 
INNER JOIN Vis_Armazem ON Tabelastock .Sloc=Vis_Armazem.Sloc 
WHERE Vis_Armazem.mostrar=1 AND Tabelastock.data=(select dateadd(dd,0, cast(getdate() as date))) 
AND Tabelastock.referencia=tabela1 .referencia
group by referencia)
where tabela1.dataTmp=(select dateadd(dd,0, cast(getdate() as date)))

sql sql-update stored-procedures checksum

asked by anonymous 18.02.2017 / 23:47

1 answer

String Parameter in iReport Error while running migrated project from django 1.7 to django 1.10

score 0 · Answer 1

After some research time here is:

UPDATE Matrix SET nStock=COALESCE((
SELECT sum(stock) as TotalStock               
FROM stock 
INNER JOIN Vis_Armazem ON stock.Sloc=Vis_Armazem.Sloc 
WHERE Vis_Armazem.mostrar=1 AND stock.data=(select dateadd(dd,0, cast(getdate() as date))) 
AND stock.referencia=Matrix.referencia
group by referencia
),0)
FROM Matrix 
where Matrix.dataTmp=(select dateadd(dd,0, cast(getdate() as date)))

"COALESCE" works over integer SELECT and substitutes for a value if not found, in this case "0"