• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Formatting derived output in MS SQL view

I have this derived column in a SQL view:

case when l.qty_comp != 0
then round(l.qty_scrap / l.qty_comp * 100, 0)
else 0
end [scrap_percent]

qty_comp and qty_scrap in the source table are both defined as decimal (13,4)

The output looks like this:

14.000000000000000000  (the actual results followed by the decimal point and 18 zeroes.

How can I get the output to look like a whole number, e.g, 14?

And, why does it format the data this way?



Thanks.
0
g_johnson
Asked:
g_johnson
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
If you always want it to appear as an integer:

case when l.qty_comp != 0
then cast(round(l.qty_scrap / l.qty_comp * 100, 0)) as integer
else 0
end [scrap_percent]

When doing integer division (14 / 7) the result will be an integer.
When doing decimal division (14.0 / 7.0) the result will be a decimal.

If you need one or the other, you need to cast the data type.
0
 
g_johnsonAuthor Commented:
Thank you.  I thought I had tried that and gotten an error, but it works.  The error was coming from elsewhere ...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now