Nested operators

Hi all
I would like to submit a question to the SQL Server expert group about a
problem that I am having related with nested operators; it may seem somethin
g
foolish to ask but it is driving me crazy since 4 hours ago:
I am using a case statement that looks like this (I put it simplified just
for not being too long):
…..
CASE WHEN Field1 = ‘A’
THEN Field2*12/44
ELSE 0
END
….
The thing is that if I put the second line as shown above, it works well, it
gives me the right result.
But, if I put it as follows:
THEN Field2*(12/44)
It always gives me zero (0) in the result… and I don’t understand WHY
Even though this case can be easily fixed by excluding the parentheses; how
can it be managed in another case where the nesting must be specified?
Any ideas would be appreciated
Thanks in advance,
AnaIn your first example, I'll guess that Field2 is a float or decimal so it's
promoting 12 to decimal/float and then doing the same to 44.
In the second example, because of the parentheses, it's doing the division
first (12/44).
Because both numbers are integers, the result is 0.
Change Field2*(12/44) to Field2*(12.0/44) or cast one of the 2 numbers to
float or decimal and try it again.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:D45BDEBC-C934-4B3F-BCDD-7FD65BE8213B@.microsoft.com...
> Hi all
> I would like to submit a question to the SQL Server expert group about a
> problem that I am having related with nested operators; it may seem
> something
> foolish to ask but it is driving me crazy since 4 hours ago:
> I am using a case statement that looks like this (I put it simplified just
> for not being too long):
> ...
> CASE WHEN Field1 = 'A'
> THEN Field2*12/44
> ELSE 0
> END
> ..
> The thing is that if I put the second line as shown above, it works well,
> it
> gives me the right result.
> But, if I put it as follows:
> THEN Field2*(12/44)
> It always gives me zero (0) in the result. and I don't understand WHY
> Even though this case can be easily fixed by excluding the parentheses;
> how
> can it be managed in another case where the nesting must be specified?
> Any ideas would be appreciated
> Thanks in advance,
> Ana
>|||I believe it is evaluating 12/44 as an integer value. this is around 0.27,
which gets rounded to 0 to make it an integer.
Add in a cast to make this work correctly...
Then cast(field2 as decimal(8,1) * (cast(12 as decimal(8,1)/cast(44 as
decimal(8,1))
Or, alternatively, the following should work, but the conversion is
implicit, not explicit...
THEN Field2*(12.0/44)
Adding the .0 after the number casts it as a decimal, and the calculation
will return a decimal value.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:D45BDEBC-C934-4B3F-BCDD-7FD65BE8213B@.microsoft.com...
> Hi all
> I would like to submit a question to the SQL Server expert group about a
> problem that I am having related with nested operators; it may seem
something
> foolish to ask but it is driving me crazy since 4 hours ago:
> I am using a case statement that looks like this (I put it simplified just
> for not being too long):
> ...
> CASE WHEN Field1 = 'A'
> THEN Field2*12/44
> ELSE 0
> END
> ..
> The thing is that if I put the second line as shown above, it works well,
it
> gives me the right result.
> But, if I put it as follows:
> THEN Field2*(12/44)
> It always gives me zero (0) in the result. and I don't understand WHY
> Even though this case can be easily fixed by excluding the parentheses;
how
> can it be managed in another case where the nesting must be specified?
> Any ideas would be appreciated
> Thanks in advance,
> Ana
>|||My guess it the datatype of the Field2 is INT...
Without the brackets the order of execution leads to implicit
conversion to another more compatible type, whereas the second forces
an INT to be created since you multiply the another number by an INT
explicitly.|||Look at the change in results when you use 12/44 vs. 12.0/44.0
Try not to depend on the implicit data type conversion.|||Raymond, thank you very much.
I applied what you said and it works well now.|||No problem Ana.
Interesting problem.
I've seen many "integer division" questions asked here.
But it's the first time I've seen one like this.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:DE51690B-E471-450E-A7F3-FEC2D8AF932D@.microsoft.com...
> Raymond, thank you very much.
> I applied what you said and it works well now.|||Johny D,
Can you please elaborate? When I run the code 12/44 I get 0, but 12.0/44 =
12/44.0 = 12.0/44.0 = .272727.
The implicit conversion seems to return the correct values.
However, I do avoid implicit conversion and use explicit conversion myself,
for two reasons (perhaps this is your point?)
1. The next programmer/DBA to view the code knows that the conversion is
happening.
2. If the rules for implicit conversion ever change, my code will still
work.
Was this your point?
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1147118556.054867.100550@.i39g2000cwa.googlegroups.com...
> Look at the change in results when you use 12/44 vs. 12.0/44.0
> Try not to depend on the implicit data type conversion.
>|||Yes that was exactly my point...
My point was that without the decimal point, SQL Server is assuming the
values are INTEGERs, hence you always end up multiplying your value by
0 since 12/44 as an INT evaluates to 0.
However with the decimal specified, SQL Server then uses a more
appropriate datatype.
My assumption is that FLOAT is used by default when the '.' is
specified. if you prefer to specify the precision and scale you could
define the value as DECIMAL(10,4)
e.g.
DECLARE @.c_factor DECIMAL(10,4)
SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
correct typing
PRINT @.c_factor
DECLARE @.c_factor FLOAT
SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
correct typing
PRINT @.c_factor|||Ahhhh...
I had forgotten that the '.' casts the number to float rather than decimal.
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1147185919.270887.310080@.e56g2000cwe.googlegroups.com...
> Yes that was exactly my point...
>
> My point was that without the decimal point, SQL Server is assuming the
> values are INTEGERs, hence you always end up multiplying your value by
> 0 since 12/44 as an INT evaluates to 0.
> However with the decimal specified, SQL Server then uses a more
> appropriate datatype.
> My assumption is that FLOAT is used by default when the '.' is
> specified. if you prefer to specify the precision and scale you could
> define the value as DECIMAL(10,4)
> e.g.
> DECLARE @.c_factor DECIMAL(10,4)
> SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
> correct typing
> PRINT @.c_factor
>
> DECLARE @.c_factor FLOAT
> SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
> correct typing
> PRINT @.c_factor
>