Hello everyone. Can anyone tell me why the result of the query below is bringing in the first 4 records the Zeroing Balance?

Question

Hello everyone. Can anyone tell me why the result of the query below is bringing in the first 4 records the Zeroing Balance?

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

Can anyone tell me why the result of the query below is bringing the Balance to zero in the first 4 records? I'm calculating the inputs plus the outputs. Calculates correctly if there are values in the two columns, but when there is no value in the [SAI] column the result also gets zero.

What I expected: ENT = 10 + SAI = 0 = > SLD = 10

Query:

select t1.Z1_COD, 
     (SELECT SUM(t2.Z1_QTD) FROM SZ1010 t2 WHERE t1.Z1_COD=t2.Z1_COD AND t2.Z1_TPMOV = 'E' ) as ENT,
     (SELECT SUM(t3.Z1_QTD) FROM SZ1010 t3 WHERE t1.Z1_COD=t3.Z1_COD AND t3.Z1_TPMOV = 'S' ) as SAI, 
     (SELECT SUM(t2.Z1_QTD) FROM SZ1010 t2 WHERE t1.Z1_COD=t2.Z1_COD AND t2.Z1_TPMOV = 'E' ) + (SELECT SUM(t3.Z1_QTD) FROM SZ1010 t3 WHERE t1.Z1_COD=t3.Z1_COD AND t3.Z1_TPMOV = 'S' )  as SLD
     FROM  SZ1010 t1

Result

sql sql-server

asked by anonymous 02.03.2018 / 21:44

2 answers

How to drill down to an entity from the STP @PRIMAVERA module AsyncTask with one or more methods

score 2 · Answer 1

Possibly there are no records in the given conditions, so it is recommended to put ISNULL (column, 0) so that if there is no data, then it is set to zero.

However, to get the data you want, you can do an INNER QUERY (query inside another), as follows:

SELECT Z1_COD, ENT, SAI, ENT - SAI AS SLD
FROM
(
    select t1.Z1_COD, 
         ISNULL((SELECT SUM(t2.Z1_QTD) FROM SZ1010 t2 WHERE t1.Z1_COD=t2.Z1_COD AND t2.Z1_TPMOV = 'E' ), 0) as ENT,
         ISNULL((SELECT SUM(t3.Z1_QTD) FROM SZ1010 t3 WHERE t1.Z1_COD=t3.Z1_COD AND t3.Z1_TPMOV = 'S' ), 0) as SAI, 
         FROM  SZ1010 t1
) T

score 0 · Answer 2

Try to do the following:

SELECT 
    T1.Z1_COD, 
    (SELECT ISNULL(SUM(T2.Z1_QTD),0) FROM SZ1010 T2 WHERE T1.Z1_COD=T2.Z1_COD AND T2.Z1_TPMOV = 'E' ) AS ENT,
    (SELECT ISNULL(SUM(T3.Z1_QTD),0) FROM SZ1010 T3 WHERE T1.Z1_COD=T3.Z1_COD AND T3.Z1_TPMOV = 'S' ) AS SAI, 
    (SELECT ISNULL(SUM(T2.Z1_QTD),0) FROM SZ1010 T2 WHERE T1.Z1_COD=T2.Z1_COD AND T2.Z1_TPMOV = 'E' ) + (SELECT ISNULL(SUM(T3.Z1_QTD),0) FROM SZ1010 T3 WHERE T1.Z1_COD=T3.Z1_COD AND T3.Z1_TPMOV = 'S' )  AS SLD
FROM  
    SZ1010 T1