You can simplify the iif() expressions a bit by using the max() function instead, for example:
... sum(max(column5, 0))
>hi all,
>
>i try this code to get result as 9 ,30.43,3,7
>but the result is 9,30,43,10,10
>
>SELECT column27,;
> CAST(sum(IIF((column5>0) , column5, 0)) AS N(14,2)) AS File3,;
> CAST(sum(IIF((column8>0) , column8, 0)) AS N(14,2)) AS File4,;
> CAST(count(IIF((column5>0) , column5, 0)) AS N(4)) AS File5,;
> CAST(count(IIF((column8>0) , column8, 0)) AS N(4)) AS File6;
> FROM wtable2 ;
>GROUP BY column27;
>INTO CURSOR crsTest
>index on padl(alltr(column27),10,"0") tag aaa
>BROW NORMAL
>
>
>Column27 column3 column4 Column5 Column8
>9 a 42 12 0
>9 a 42 0 15
>9 a 42 0 12
>9 a 42 0 7
>9 a 42 5 0
>9 a . 0 3
>9 a . 0 1
>9 a . 0 1
>9 a . 0 4
>9 a 42 13 0
>
>result as
>Column27 sum(column5) sum(column8) count(column5) count(column8)
> 9 30 4 3 7
>
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
