Skip to Content
0
May 11, 2018 at 07:46 AM

SAP HANA -count(*) giving different outputs

1676 Views Last edit May 11, 2018 at 07:52 AM 6 rev

select count(*)from(SELECT
A.MATNR ARTICLE,
A.WERKS SITE, 
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.DMBTR)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.DMBTR)END),0)))) DUMP_VALUE, 
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.MENGE)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.MENGE)END),0)))) DUMP_QTY                   
FROM MSEG A 
INNERJOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO         
INNERJOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL                
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)AND A.BWART IN('551','552','951','952')AND C.LEVEL2=10AND B.FORMAT_CD IN('1')GROUPBY A.MATNR,A.WERKS);

Output count is - 129790
IF i just change clause AND B.FORMAT_CD IN ('1') to AND B.FORMAT_CD IN (1), like below -
select count(*) from(
SELECT  
A.MATNR ARTICLE,
A.WERKS SITE, 
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.DMBTR) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.DMBTR) END),0)))) DUMP_VALUE, 
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.MENGE) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.MENGE) END),0)))) DUMP_QTY                   
FROM MSEG A 
INNER JOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO         
INNER JOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL                
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)
AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)                       
AND A.BWART IN ('551','552','951','952')                                       
AND C.LEVEL2=10                                                
AND B.FORMAT_CD IN (1)      
GROUP BY A.MATNR,A.WERKS);

Ouput count - 29403 (which is correct count)


Datatype of B.FORMAT_CD is NVARCHAR(3).


How does count behave differently?