# COALESCE

• This was removed by the editor as SPAM

• Good question, thanks

...

• A simple one to end the week. Thanks.

• Easy one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

• I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

[font="Courier New"]print @float = 1

select @float = .5

[/font]

Can somebody explain why that is?

• tom.w.brannon (1/29/2016)

I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

[font="Courier New"]print @float = 1

select @float = .5

[/font]

Can somebody explain why that is?

Probably it is a rounding from .5 to 1.

What type is @float ?

Iulian

• I spent way to long on this one looking for the data type precedence catch. :crazy:

• tom.w.brannon (1/29/2016)

I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

[font="Courier New"]print @float = 1

select @float = .5

[/font]

Can somebody explain why that is?

Can you post the exact code that you're using? Float shouldn't be rounded to 1.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Sneaky. When I first looked at it, I took 5,500 to be 5500 instead of two distinct arguments.

Don Simpson

• Luis Cazares (1/29/2016)

tom.w.brannon (1/29/2016)

I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

[font="Courier New"]print @float = 1

select @float = .5

[/font]

Can somebody explain why that is?

Can you post the exact code that you're using? Float shouldn't be rounded to 1.

Sorry, I made a mistake. Both print and select give back .5. Not sure what I was looking at.

DECLARE @int1 INT,

@int2 INT,

@float FLOAT

SET @float = COALESCE

(@int1,

@int2,

.5)

SELECT COALESCE

(@int1,

5,500)

print @float;

select @float;

• Hmm. I thought COALESCE will return the datatype of the leftmost parameter. Learnt something. Thanks.

Hmm. I thought COALESCE will return the datatype of the leftmost parameter. Learnt something. Thanks.

ISNULL() does work that way.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Simple one 🙂

• Still a week-end question for tired persons.

It is an easy question to reply as soon as you know that a variable declared thru the Declare statement is always initialized to a null value when it is not initialized inside the Declare or by a SET statement ( before its 1st use.

I recognize that I have forgotten this "special" way of initialization up to a session of SQL Server Days related to the basics of T-SQL I participated for the release of SQL Server 2008 R2. As it is now anchored in my memory , the choice was easy. A prof that even a basic session can be useful for a man who began with SQL Server 6.5. Since I have used nearly all versions except the 7.0 . I have to recognize that I am using T-SQL but it is not my favorite topic.

Anyway , thanks for this easy question which permits to dig deeply inside my old memory.

Viewing 15 posts - 1 through 15 (of 16 total)