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...
> 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
--

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Multiple Variables Assigned To One Select

Hello,

Is there a way to assign multiple variables to one select statement as in the following example?

DECLARE @.FirstName VARCHAR(100)

DECLARE @.MiddleName VARCHAR(100)

DECLARE @.LastName VARCHAR(100)

@.FirstName, @.MiddleName, @.LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'

I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.

Thank you!

Cody

Hi, you can use the below syntax.

SELECT

@.FirstName = FirstName,

@.MiddleName = MiddleName,

@.LastName = LastName

FROM USERS

WHERE username = 'UniqueUserName'

Eralper

http://www.kodyaz.com

Multiple select statment inside a stored procedure

Hi there !
Recently i had written a stored procedure which contain multiple select
statment. One follow by anyother, like the following,
declare @.nTemp int
select count(*) AS TempCount from tableA where conditionA
select @.nTemp = TempCount
select count(*) from tableB where condtionB
select count(*) from tableC where conditionC
select count(*) from tableD where conditionD
...
When i run the store procedure, strange result had been return,
the count from tableA give correct result.
the count from tableB give correct result.
but start from there,
the count(*) from tableC return the same result as B
and the count(*) from tableD return same result as B as well.
If i just run the select count(*) statment from B, C, D individually,
3 different result had been return ( as expected the result would not
be the same. ).
It happens only when i move this store procedure from 1 server to
another.
it sounds like some setting of the server causing this problem. Can
someone help please ?Noodle wrote:
> Hi there !
> Recently i had written a stored procedure which contain multiple select
> statment. One follow by anyother, like the following,
> declare @.nTemp int
> select count(*) AS TempCount from tableA where conditionA
> select @.nTemp = TempCount
I am surprized the above does not give you an error. I would
write it like this:
select @.nTemp = count(*) from tableA where conditionA|||when you write a stored procedure with multiple result sets the statements
should be ended with a ;
in a supporting data provider in a program language you can then even access
all result sets ( like the .Net sql client provider ) with .nextresult or a
simular method
regards
Michel Posseth [MCP]
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:i8lQe.602$yQ1.361@.newssvr17.news.prodigy.com...
> Noodle wrote:
> I am surprized the above does not give you an error. I would
> write it like this:
> select @.nTemp = count(*) from tableA where conditionA|||On 28 Aug 2005 06:37:51 -0700, Noodle wrote:

>Hi there !
>Recently i had written a stored procedure which contain multiple select
>statment. One follow by anyother, like the following,
>declare @.nTemp int
>select count(*) AS TempCount from tableA where conditionA
>select @.nTemp = TempCount
(snip)
Hi Noodle,
Please post the exact code of your procedure, or at least a working
repro that you have actually tested on your system and that you have
witnessed displaying the same behaviour as your real proc.
The code you posted will throw an error on any server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ok, it is my mistake, i didn't write out the code. See if you guys
encounter this problem before
DECLARE @.nTemp smallint
SELECT @.nTemp = MAX(M_INDEX) FROM TABLE_A WHERE RECORD_ID =
@.REC_ID
SELECT @.nTemp AS MAXIMUM_INDEX
--First , get total record
SELECT Count(*) AS TOTAL_RECORD FROM TABLE_B WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp
--Second , get total record with error
SELECT Count(*) AS TOTAL_RECORD_WITH_ERROR FROM TABLE_C WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(ERR_CODE <> NULL OR ERR_CODE <> '')
--Thrid , get total record with conflict
SELECT Count(*) AS TOTAL_RECORD_WITH_INV_CONFLICT FROM TABLE_D
WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(INV_CONFLICT <> '0' AND INV_CONFLICT <> NULL )
..
The first ,second and third select all return the same value.
But if i just copy out and execute the select statment individually
the value is different.|||Hi Hugo,
I had posted the code in my previous post.
Have u see this kind of problem before ?
It is kinda strange, it just seems like the first 'select count(*)'
never release its buffer, and make the following select count(*) return
back the same value.
I am sure that the data in the server is correct, if i copy out the
select statment, each of them and run it individually on the query
analyzer it returns back 3 different value.
Is there any setting or option in SQL Server need to be set ?
Can any one help ? This problem already drag me a day...
Thanks...
Hugo Kornelis wrote:
> On 28 Aug 2005 06:37:51 -0700, Noodle wrote:
>
> (snip)
> Hi Noodle,
> Please post the exact code of your procedure, or at least a working
> repro that you have actually tested on your system and that you have
> witnessed displaying the same behaviour as your real proc.
> The code you posted will throw an error on any server.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Guys,
Thanks for your help.
Problem solved.
Some developer update the data wrongly.
sorry for all the trouble.|||By the way,
What is the difference between comparing NULL and Char(0)
in SQL server ?|||Noodle,
Is it possible that the Query Analyzer session has a different
setting of ANSI_NULLS than when you run these together?
I suggest you change each occurrence of
<> NULL
to
IS NOT NULL
and see if you get the desired results.
Steve Kass
Drew University
Noodle wrote:

>Hi Hugo,
>I had posted the code in my previous post.
>Have u see this kind of problem before ?
>It is kinda strange, it just seems like the first 'select count(*)'
>never release its buffer, and make the following select count(*) return
>back the same value.
>I am sure that the data in the server is correct, if i copy out the
>select statment, each of them and run it individually on the query
>analyzer it returns back 3 different value.
>Is there any setting or option in SQL Server need to be set ?
>Can any one help ? This problem already drag me a day...
>Thanks...
>
>Hugo Kornelis wrote:
>
>
>|||NULL means "there is no value here". NULL is the absence of a
value, and it is not a value. CHAR(0) is a value, the one-character
string containing the single byte with value 0.
Think of your column as an envelope. If the envelope is empty, you
that column IS NULL. If the envelope contains one byte of information,
and the byte value is zero, you have CHAR(0) in the column. Empty
is different from "contains CHAR(0)"
Steve Kass
Drew University
Noodle wrote:

>By the way,
>What is the difference between comparing NULL and Char(0)
>in SQL server ?
>
>