Hello guys. Can anyone tell me why the result of the consultation below is bringing in the first 4 records the Zeroed Balance?

Asked

Viewed 80 times

2

Can anyone tell me why the result of the consultation below is bringing in the first 4 records the Zeroed Balance? I’m calculating the entrances and the exits. Calculate correctly if there are values in both columns, but when there is no value in the [SAI] column the result is also 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

Upshot Coluna ENT - SAI zerado

sql sql-server

  • And the same Z1_COD appearing several times in the first column, is it normal? I think this query will need to undergo some kind of restructuring.

  • 1

    Test the SUM as follows: SUM(ISNULL(t2.Z1_QTD, 0))

  • There are likely to be values of SAI that is null on the bench, and these are being inferred as 0 at some point in the result, but in the formula is nullifying the result as well.

2 answers

2

Possibly there are no records in the given conditions, so it is recommended to put the 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

0

Try doing it this way:

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

  • It is always interesting to explain the reason for the change in the answer. Teach to fish and do not give the fish.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.