Default Field Value

Is there a way to set default values for a numeric field? I have several
fields that sometimes are null. I want the nulls to show as zero. I tried
iif(value is null, 0.00 , value) but it gives an error on the â'nullâ' word. I
tried â'IsNullâ', â'IsNothingâ' and â'IsNumericâ' all with the same error.
Any ideas?Try
iif(value=Nothing, 0.00 , value)
"tachtenberg" <tachtenberg@.discussions.microsoft.com> escribió en el mensaje
news:9FF1A567-6AB0-402F-A542-8205EB6AA195@.microsoft.com...
> Is there a way to set default values for a numeric field? I have several
> fields that sometimes are null. I want the nulls to show as zero. I
> tried
> iif(value is null, 0.00 , value) but it gives an error on the "null" word.
> I
> tried "IsNull", "IsNothing" and "IsNumeric" all with the same error.
> Any ideas?
>

Decimals in view

Hello,

I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))

181.25
176.5
170

I want the view to show
181.25
176.50
170.00

I have tried Cast but that doesn't seem to do the job.

Help!

RolfNote that this is very poor practice to use server side code to format data. Formatting ought to be done by the client.

With that said, you can use the Str() function to format it as a string.

-PatP

decimal to numeric conversion

Hi guys,

how do i convert a decimal to numeric data type?

nhoyti:

convert a decimal to numeric data type

what i understood is, you are having a field in database with Decimal datatype and you want to retrieve it as Numeric Field...

you can use CAST() function as..

SELECT CAST(colName AS INT) FROM TABLE

here colName is the Field with Decimal DataType...

hope it helps./.

|||

i have a column in my database which is a decimal... and i want to display the decimal to numeric data type

|||

kaushalparik27:

what i understood is, you are having a field in database with Decimal datatype and you want to retrieve it as Numeric Field...

you can use CAST() function as..

SELECT CAST(colName AS INT) FROM TABLE

here colName is the Field with Decimal DataType...

did you try as above solution ??... i think it will convert Decimal to Numeric DataType...

decimal question

I am working on a bcp table dump. I have a column that has a numeric value
with 2 decimals. Is there to have the result file show no decimals if there
is no value in this table. Right now, it looks like
123.45
0.00
123.45
0.00
I would like it to look like :
123.45
0
123.45
0
I know the value is the same, but I still have a need to accomplish this.
Thanks in advancecast it to a varchar.
set nocount on
declare @.table table (value numeric (6,2))
insert into @.table values (123.45)
insert into @.table values (0.00)
insert into @.table values (123.45)
insert into @.table values (0.00)
select case when value = 0 then '0'
else str(value,6,2) end as value
from @.table
What if the value is 123.40, what then?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"TJBowens" <TJBowens@.msn.com> wrote in message
news:OXTJVPWNFHA.3620@.TK2MSFTNGP10.phx.gbl...
>I am working on a bcp table dump. I have a column that has a numeric value
>with 2 decimals. Is there to have the result file show no decimals if
>there is no value in this table. Right now, it looks like
> 123.45
> 0.00
> 123.45
> 0.00
> I would like it to look like :
> 123.45
> 0
> 123.45
> 0
> I know the value is the same, but I still have a need to accomplish this.
> Thanks in advance
>|||123.40
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eunvFyXNFHA.4028@.tk2msftngp13.phx.gbl...
> cast it to a varchar.
> set nocount on
> declare @.table table (value numeric (6,2))
> insert into @.table values (123.45)
> insert into @.table values (0.00)
> insert into @.table values (123.45)
> insert into @.table values (0.00)
> select case when value = 0 then '0'
> else str(value,6,2) end as value
> from @.table
> What if the value is 123.40, what then?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "TJBowens" <TJBowens@.msn.com> wrote in message
> news:OXTJVPWNFHA.3620@.TK2MSFTNGP10.phx.gbl...
>|||So this will do what you want then, right?
I assume 123.40 was the answer to my question :)
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"TJBowens" <TJBowens@.msn.com> wrote in message
news:uyYVMNfNFHA.688@.TK2MSFTNGP10.phx.gbl...
> 123.40
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eunvFyXNFHA.4028@.tk2msftngp13.phx.gbl...
>

decimal division precision

in my understanding of fixed numeric types and datatype precision rules,
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:

> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>

Decimal Datatype

I have looked at the SQL Docs, and am trying to create a test table that uses a column of Numeric Datatype. But for some reason, it is rounding to the nearest Integer as opposed to using a decimal value.

Heres the SQL I use to create the table:

CREATE TABLE Test (ID int IDENTITY(1,1), Test_Numeric numeric(2,0))

dont laugh if its obvious, cause I dont use decimal values very much :PConsult the Holy book (SQL Server Books online) .. here is what its says about numeric

Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.|||Ha ha ha!

(Oops! Sorry... :rolleyes: )|||You have to undersatnd what "precision" means...

You're "0" means no positions after the decimal...the first number means the TOTAL number of digits...

so Play with something like:

USE Northwind

CREATE TABLE Test (ID int IDENTITY(1,1), Test_Numeric numeric(4,2))

INSERT INTO TEST (Test_Numeric) SELECT 1.12

SELECT * FROM Test

DROP TABLE Test