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
smithmrkAsked:
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.

pcelbaCommented:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smithmrkAuthor Commented:
THANK YOU!!!!
I went with CAST(0 as num(12,2))

Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.