Link to home
Start Free TrialLog in
Avatar of smithmrk
smithmrkFlag for United States of America

asked on

Dropping Decimal Points??

OK, I'm stumpped on this one!

Here is my SQL Statement:
      SELECT      Item.bat_id,;
            Item.client_id,;
            SUM(IIF(Item.bal_doc=='N',1,0)) AS CorrCount,;
            SUM(IIF(Item.doc_type==STUB_TYPE,1,0)) AS StubCount,;
            SUM(IIF(Item.doc_type==STUB_TYPE,Item.appl_amt,0)) AS StubTotal,;
            SUM(IIF(Item.doc_type==CHECK_TYPE,1,0)) AS CheckCount,;
            SUM(IIF(Item.doc_type==CHECK_TYPE,Item.appl_amt,0)) AS CheckTotal;
      FROM      &Item_alias Item;
      WHERE      EMPTY(ALLTRIM(Item.Rej_job));
      GROUP BY Item.bat_id, Item.client_id;
      INTO TABLE Items

Here is what is happening...The IIF Statement that looks for the STUB_TYPE and then takes the appl_amt works FINE!!!  It creates a Total with Decmials...the LINE with the CheckTotal which is the same command using the same field to add an amount together is dropping the decimal points...see attached Screen Shot!

WHY???

Thanks,
Mark
CheckDecimals.jpg
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of smithmrk

ASKER

THANK YOU!!!!
I went with CAST(0 as num(12,2))

Mark