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.