smithmrk
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==STU B_TYPE,1,0 )) AS StubCount,;
SUM(IIF(Item.doc_type==STU B_TYPE,Ite m.appl_amt ,0)) AS StubTotal,;
SUM(IIF(Item.doc_type==CHE CK_TYPE,1, 0)) AS CheckCount,;
SUM(IIF(Item.doc_type==CHE CK_TYPE,It em.appl_am t,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
Here is my SQL Statement:
SELECT Item.bat_id,;
Item.client_id,;
SUM(IIF(Item.bal_doc=='N',
SUM(IIF(Item.doc_type==STU
SUM(IIF(Item.doc_type==STU
SUM(IIF(Item.doc_type==CHE
SUM(IIF(Item.doc_type==CHE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went with CAST(0 as num(12,2))
Mark