Skip to Content

SAP HANA -count(*) giving different outputs

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 11 at 10:38 AM

    You gave the cause for the effect away with

    Datatype of B.FORMAT_CD is NVARCHAR(3).

    When you filter the query with a non-matching data type, then HANA will perform an implicit type conversion.

    That means, it either converts the parameter of the IN clause into NVARCHAR(3) or the contents of the FORMAT_CD column to INTEGER (that was what happened on my test system).

    The filtering is then performed based on the result of the implicit conversion.

    You can check on this by using EXPLAIN PLAN.

    In the column OPERATOR_DETAILS you should find an entry similar to

    FILTER CONDITION: TO_INT(B.FORMAT_CD) = 1

    You can even check on how the values of the NVARCHAR column look like after the conversion by selecting TO_INT(FORMAT_CD).

    Add comment
    10|10000 characters needed characters exceeded