Hi,
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!|||It gives you control of the input datatype so you can determine over which datatypes the operation
will occur.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
>> Hi,
>> What's wrong with this:
>> DECLARE @.a as float
>> DECLARE @.b as float
>> SET @.a = (0.25) * 100
>> SET @.b = (1/4) * 100
>> print @.a
>> print @.b
>> Returns:
>> 25
>> 0
>> Why is the following returning 0 and not 25?
>> SET @.b = (1/4) * 100
>> Something similar is coded somewhere in one of our apps and is causing
>> incorrect reporting.
>> Thanks!|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts
Friday, March 23, 2012
multiply by 1/4
Hi,
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!
gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
|||It gives you control of the input datatype so you can determine over which datatypes the operation
will occur.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...[vbcol=seagreen]
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!
gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
|||It gives you control of the input datatype so you can determine over which datatypes the operation
will occur.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...[vbcol=seagreen]
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
multiply by 1/4
Hi,
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!|||It gives you control of the input datatype so you can determine over which d
atatypes the operation
will occur.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...[vbcol=seagreen]
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
>|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!|||It gives you control of the input datatype so you can determine over which d
atatypes the operation
will occur.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...[vbcol=seagreen]
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
>|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Wednesday, March 21, 2012
Multiple where clause help please
Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
Multiple where clause help please
Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
Jake
Nevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
sql
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
Jake
Nevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
sql
Multiple where clause help please
Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
Multiple variables on a Script transformation
I must be doing something wrong here but I can't see what it is. Can anyone enlighten me?
I have a script transformation that I want to pass two variables to through ReadOnlyVariables but for some reason the editor will only accept one. If I try to add a second I get the message "The variable cannot be found".
The help text against ReadOnlyVariables says to enter a comma separated list which I thought would be simply "variable1, variable2" but this doesn't seem to work. I can enter each variable name individually so it is not that I have the names wrong.
Can anyone advise?
Thanks,
AndrewYep, don't put a space between the variables.
i.e. "variable1,variable2"|||Thanks, it hadn't occurred to me that the editor would be quite so fussy. Is this documented anywhere?|||BOL states "Type a comma-separated list of read-only variables that are available to the script." I've submitted feedback saying that perhaps it should mention there mustn't be any spaces in there.
-Jamie|||I'll make the BOL text clearer today. I'm surprised that I didn't hit the same problem when testing. That is rather fussy on the editor's part.
-Doug
Subscribe to:
Posts (Atom)