# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
This depends on the first result of your IIF condition. It is always better to "reserve" some decimal places (see 00000000.00 in the below code) or use CAST() function to derive the result data type:

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,00000000.00)) AS StubTotal,;
SUM(IIF(Item.doc_type==CHECK_TYPE,1,0)) AS CheckCount,;
SUM(IIF(Item.doc_type==CHECK_TYPE,Item.appl_amt,00000000.00)) AS CheckTotal;
FROM      &Item_alias Item;
WHERE      EMPTY(ALLTRIM(Item.Rej_job));
GROUP BY Item.bat_id, Item.client_id;
INTO TABLE Items

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,CAST(0 AS num(12,2)))) AS StubTotal,;
SUM(IIF(Item.doc_type==CHECK_TYPE,1,0)) AS CheckCount,;
SUM(IIF(Item.doc_type==CHECK_TYPE,Item.appl_amt,CAST(0 AS num(12,2))) AS CheckTotal;
FROM      &Item_alias Item;
WHERE      EMPTY(ALLTRIM(Item.Rej_job));
GROUP BY Item.bat_id, Item.client_id;
INTO TABLE Items
0

Experts Exchange Solution brought to you by