How to format calculation - Cast, Convert or int? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to format calculation - Cast, Convert or int?

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2009-09-02 : 10:00:29

I have a case statement in my view as follows....

Sum (case when POP30310.ponumber = sop60100.ponumber AND
POP30310.trxsorce like 'POIVC%' then POP30310.EXTDCOST/POP30310.UNITCOST else '0' end) as QTYINVOICEDMATCHED

the result is being shown as 3.000000000000000000

the result should always be a whole number in this case '3'

Where in the statement do i format my results? I researched books on line - I think I need to use a CAST/int combination or Convert or just int? I could not figure out where to put it.....i tried at the start of the statement and it didn't work......i tried immediately after then and that didn't work.....

thanks in advance.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:03:25

Round(Sum (case when POP30310.ponumber = sop60100.ponumber AND
POP30310.trxsorce like 'POIVC%' then POP30310.EXTDCOST/POP30310.UNITCOST else '0' end),0) as QTYINVOICEDMATCHED

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 10:13:12

UnitCost will probably never be 0 but it would be good practice to guard against a devide-by-zero error. So just add an additional condition to your CASE. Something like this will guarantee a 0 if UnitCost is NULL or 0.

Round(Sum (case when POP30310.ponumber = sop60100.ponumber AND
POP30310.trxsorce like 'POIVC%' AND nullif(POP30310.UNITCOST,0) is NOT NULL then POP30310.EXTDCOST/POP30310.UNITCOST else '0' end),0) as QTYINVOICEDMATCHED

EDIT:
actually the round will just make sure you decimals are 0. You should use your front end application to format the result. But if you need an int then CONVERT the rounded value to INT. One other thing, the ELSE set the value to a string: '0'. That should be the same datatype as the other CASEs. So maybe this:

select CONVERT(int, Round(Sum (case when POP30310.ponumber = sop60100.ponumber AND
POP30310.trxsorce like 'POIVC%' AND nullif(POP30310.UNITCOST,0) is NOT NULL then POP30310.EXTDCOST/POP30310.UNITCOST else 0 end),0)) as QTYINVOICEDMATCHED

Be One with the Optimizer
TG

btamulis
Yak Posting Veteran

64 Posts

Posted - 2009-09-02 : 10:27:24

Thanks for replies.

I tried both suggestions and i get same results.

I researched the round syntax and i agree it should work.

Any other thoughts?

Thanks again......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:31:11

quote:
Originally posted by btamulis

Thanks for replies.

I tried both suggestions and i get same results.

I researched the round syntax and i agree it should work.

Any other thoughts?

Thanks again......

did you try convert(int,round(...))

btamulis
Yak Posting Veteran

64 Posts

Posted - 2009-09-02 : 10:31:52

TG - the convert did the trick.......thanks a bunch!

Regards....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources