I have my request where co.DATE_UTILISATION is an integer can be null :

SELECT COUNT(DISTINCT co.NUMERO) as TOTAL ,
co.DATE_UTILISATION,
cast(NULL as int) as TEST
FROM FOIATST.coupons as co
LEFT OUTER JOIN FOIATST.operateurs as op ON co.OPERATEUR = op.CODE
WHERE co.SOCIETTE = 999
AND co.DATE_DEB_VALIDITE = 20131007 AND co.DATE_FIN_VALIDITE = 20140107
AND co.ETAT = 'UTILISE '
AND co.DATE_DEB_VALIDITE is not NULL AND co.DATE_UTILISATION <> 0
GROUP BY DATE , ETAT

i 've tested cast(NULL as int) as TEST from DB2: Won't Allow "NULL" column?

my request works well without "co.DATE_UTILISATION, " can you show me my mistake please ?

thanks

The problem is that you are grouping by one column but selecting another.

Try this:

SELECT COUNT(DISTINCT co.NUMERO) as TOTAL ,
       co.DATE_UTILISATION,
       cast(NULL as int) as TEST
FROM FOIATST.coupons as co
LEFT OUTER JOIN FOIATST.operateurs as op ON co.OPERATEUR = op.CODE
WHERE co.SOCIETTE = 999
AND co.DATE_DEB_VALIDITE = 20131007 AND co.DATE_FIN_VALIDITE = 20140107
AND co.ETAT = 'UTILISE '
AND co.DATE_DEB_VALIDITE is not NULL AND co.DATE_UTILISATION <> 0
GROUP BY co.DATE_UTILISATION, ETAT;

Also, the inclusion of etat in group by is not necessary, because you are filtering it down to one value anyway.

more