none
Gross field does not accept null value

    Question

  • Dear all,

                When I use the following sql it will return a null value. And I get  Gross does not accept null value. How can I correct the sql?

    select (sum(price-tracom-cost)-iif(tr.name='abc',0000000,team_count * 500)) gross from table1

    It seems sum and iif cannot be used together. How can I correct it? Thanks


    hon123456
    Saturday, January 15, 2011 3:15 AM

Answers

  • Dear all,

               I have found out the solution. Because some field is null. So I changed to Sum(iif(isnull(price),0,price-iif(isnull(tracom),0,tracom)-iif(iif(isnull(cost),0,cost) - iif(tr.name='abc',0000000,team_count * 500). it is ok now. Thanks.


    hon123456
    • Marked as answer by hon123456 Monday, January 17, 2011 8:39 AM
    Monday, January 17, 2011 8:39 AM

All replies

  • The whole command is somehow suspicious... What it should calculate?

    select sum(price-tracom-cost) FROM table1   && OK, this should calculate one number from the whole table

    iif(tr.name='abc',0000000,team_count * 500)  &&  here you have to explain what means tr.name and team_count

    tr.name='abc'  is a constant during SQL command evaluation because "tr" alias isn't used in SELECT command

    team_count  ... could be a variable or table column - you should explain it...

    Saturday, January 15, 2011 10:29 AM
  • I guess you want to actually put that into the sum, which you can:

    Select Sum(price-tracom-cost-iif(tr.name='abc',0000000,team_count * 500)) as gross from table1

    What you can't do is Sum(expression), which is a result over all records and then subtract another expression, which is for one row. you also can't do Sum(price)-tracom. Put everything into the expression within Sum().

    Bye, Olaf.

    Saturday, January 15, 2011 5:41 PM
  • Dear all,

               I have found out the solution. Because some field is null. So I changed to Sum(iif(isnull(price),0,price-iif(isnull(tracom),0,tracom)-iif(iif(isnull(cost),0,cost) - iif(tr.name='abc',0000000,team_count * 500). it is ok now. Thanks.


    hon123456
    • Marked as answer by hon123456 Monday, January 17, 2011 8:39 AM
    Monday, January 17, 2011 8:39 AM