Increase nvarchar field value like a num

Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard

Incorrect syntax near ?.

Hi Guys,

I have moved my asp.net app from access db over to MS SQL 2005 DB.

And I have got a slight problem when I go to view any product

for example if I type in the url ofhttp://domain.com/catalog/Details.aspx?AdNum=1

I get this error

Server Error in '/catalog' Application.
------------------------

Incorrect syntax near '?'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '?'.

I have attached the details.aspx.

I await for some suggestions.

Thanks

Matthew

------

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>23<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">4Ad Detail - <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />5</asp:Content>67<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >89<script runat="server">10Sub Page_Load(ByVal SenderAs Object,ByVal EAs EventArgs)11If Not IsPostBackThen12 If Request.QueryString("AdNum") =""Then13 Response.Redirect("default.aspx")14End If15 EditLink.NavigateUrl ="confirm.aspx?AdNum=" & Request.QueryString("AdNum")16End If17 End Sub1819 Protected Sub DetailsView1_PageIndexChanging(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewPageEventArgs)2021End Sub22</script>2324  25 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"26 CellPadding="4" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" ForeColor="#333333"27 GridLines="None" Height="65px" Width="100%" Font-Names="Arial" Font-Size="8pt" OnPageIndexChanging="DetailsView1_PageIndexChanging">28 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />29 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />30 <Fields>31 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />32 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />33 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />34 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />35 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />36 <asp:TemplateField HeaderText="Email">37 <ItemTemplate>38 <asp:HyperLink ID="HyperLink1" runat="server" Text=Email NavigateUrl='<%# Eval("Email", "mailto:{0}") %>' />39 </ItemTemplate>40</asp:TemplateField>41 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />42 </Fields>43 </asp:DetailsView>44<p><i>To respond to this ad, just click the email address45above to send the poster46 a message.</i></p>47If you created this ad, you can48<asp:hyperlink id="EditLink" runat="server" >edit or delete it.</asp:hyperlink> <br>49 <asp:SqlDataSource ID="SqlDataSource1" runat="server"50 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"51 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"52 SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = ?)">53 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 </asp:SqlDataSource>5758</asp:content>

When you use SQLDataSource, you need to use the named parameter instead of the "?" which is correct when you were using Access db. Change your SelectCommand to: SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = @.AdNum)">|||

Thanks for your help, it now works.

|||

This piece of code is suppose to allow me to edit / delete records, I can update the info and press update but it doesn't update the database. And I can press Delete record and it doesn't delete the record out of the database.

I don't get any error messages.

Below is the code:

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>2<%@. ImportNamespace="System.Data" %>3<%@. ImportNamespace="System.Data.SqlClient" %>45<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">6Edit Ad</asp:Content>78<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >910<script runat="server">11Protected Sub DetailsView1_ItemUpdated(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)12 Response.Redirect("default.aspx")13End Sub1415 Protected Sub DetailsView1_ItemDeleted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewDeletedEventArgs)16 Response.Redirect("default.aspx")17End Sub1819 Protected Sub DetailsView1_ItemCommand(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewCommandEventArgs)20If e.CommandName ="Cancel"Then21 Response.Redirect("default.aspx")22End If23 End Sub24</script>2526To make changes, click Edit, make your changes, then click Update.To delete27 this ad, just click the Delete button.28 <br />29 <br />30<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated" OnItemDeleted="DetailsView1_ItemDeleted" OnItemCommand="DetailsView1_ItemCommand">31 <Fields>32 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />33 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />34 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />35 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />36 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />37 <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />38 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />39 <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" SortExpression="UserPassword" />40 <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />41 </Fields>42 <RowStyle BackColor="#FFFBD6" />43 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />44</asp:DetailsView>45 46<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"47 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"48 DeleteCommand="DELETE FROM [Ads] WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword"49 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"50 SelectCommand="SELECT [AdNum], [Title], [Category], [Description], [Price], [Phone],, [State], [UserPassword] FROM [Ads] WHERE ([AdNum] = @.AdNum)"51 UpdateCommand="UPDATE [Ads] SET [Title] = @.Title, [Category] = @.Category, [Description] = @.Description, [Price] = @.Price, [Phone] = @.Phone, = @.Email, [State] = @.State, [UserPassword] = @.UserPassword WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword">5253 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 <DeleteParameters>57 <asp:Parameter Name="original_AdNum" Type="Int32" />58 <asp:Parameter Name="original_Title" Type="String" />59 <asp:Parameter Name="original_Category" Type="String" />60 <asp:Parameter Name="original_Description" Type="String" />61 <asp:Parameter Name="original_Price" Type="Decimal" />62 <asp:Parameter Name="original_Phone" Type="String" />63 <asp:Parameter Name="original_Email" Type="String" />64 <asp:Parameter Name="original_State" Type="String" />65 <asp:Parameter Name="original_UserPassword" Type="String" />66 </DeleteParameters>67 <UpdateParameters>68 <asp:Parameter Name="Title" Type="String" />69 <asp:Parameter Name="Category" Type="String" />70 <asp:Parameter Name="Description" Type="String" />71 <asp:Parameter Name="Price" Type="Decimal" />72 <asp:Parameter Name="Phone" Type="String" />73 <asp:Parameter Name="Email" Type="String" />74 <asp:Parameter Name="State" Type="String" />75 <asp:Parameter Name="UserPassword" Type="String" />76 <asp:Parameter Name="original_AdNum" Type="Int32" />77 <asp:Parameter Name="original_Title" Type="String" />78 <asp:Parameter Name="original_Category" Type="String" />79 <asp:Parameter Name="original_Description" Type="String" />80 <asp:Parameter Name="original_Price" Type="Decimal" />81 <asp:Parameter Name="original_Phone" Type="String" />82 <asp:Parameter Name="original_Email" Type="String" />83 <asp:Parameter Name="original_State" Type="String" />84 <asp:Parameter Name="original_UserPassword" Type="String" />85 </UpdateParameters>8687 </asp:SqlDataSource>8889</asp:content>

I appricate your help

Thanks Matthew

|||

Hi,

You can get information through these links:

http://www.asp.net/learn/dataaccess/tutorial50vb.aspx?tabid=63

http://forums.asp.net/thread/1172520.aspx

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns th
e
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if th
is
> problem might be Windows or hardware based. I did this same test on sever
al
> different machines, (dual Xeons and P4 laptops) and every time it returns
the
> incorrect result.

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.
This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.
|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 â'1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.