October 19, 2018 at 2:03 pm
The data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However, COALESCE(CONVERT(float,columnname),'') displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
October 19, 2018 at 2:15 pm
This should be handled in the presentation layer, not the database layer.
SQL Server is strongly typed. That means that you cannot mix string ('') and decimal data in the same column. This is why it's converting your '' to 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2018 at 3:38 pm
keneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)
October 19, 2018 at 7:35 pm
keneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
You're confusing a SQL table with a spreadsheet. They are totally different things. We don't do display formatting in the database; that's passed to what we call a presentation layer in the host language. We also don't use the old Sybase convert () function; we've had the ANSI/ISO standard cast () for quite a few years now. Essentially, your problem is you haven't yet learned what a tiered architecture is or what a strongly typed language is.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 20, 2018 at 3:24 am
drew.allen - Friday, October 19, 2018 2:15 PMThis should be handled in the presentation layer, not the database layer.SQL Server is strongly typed. That means that you cannot mix string ('') and decimal data in the same column. This is why it's converting your '' to 0.
Drew
I am getting the answer directly(without any conversion) in sql server 2017.
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ad34673979ff21b187a4b490952990f6
When I tried in sql server 2008 I got conversion error .My question is different version of sql server behaves differently??
Saravanan
October 20, 2018 at 5:37 am
saravanatn - Saturday, October 20, 2018 3:24 AMdrew.allen - Friday, October 19, 2018 2:15 PMThis should be handled in the presentation layer, not the database layer.SQL Server is strongly typed. That means that you cannot mix string ('') and decimal data in the same column. This is why it's converting your '' to 0.
Drew
I am getting the answer directly(without any conversion) in sql server 2017.
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ad34673979ff21b187a4b490952990f6
When I tried in sql server 2008 I got conversion error .My question is different version of sql server behaves differently??
That CASE won't return a conversion in SQL Server 2008 either; there is only one data type being returned a varchar(1), which is the value '' (the NULL will be implicitly cast to a varchar(1), as it doesn't have a defined datatype when you declared it).
I think you're misunderstanding how a CASE expression works. If you have the expression:CASE WHEN A IS NULL THEN '' END
The above can only return 2 values. The first value is '', which is when A has a value of NULL. Otherwise NULL will be returned, as no expressions within the CASE evaluated to TRUE. To return the value of A is it isn't NULL you would use:CASE WHEN A IS NULL THEN '' ELSE A END
However, then you would likely be better off using ISNULL:ISNULL(A,'')
This, however, will still suffer conversion errors for what you have.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2018 at 9:16 am
Jonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)
Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);
insert into salary values(1,92.0111);
insert into salary values(2,null);
select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salary
Saravanan
October 20, 2018 at 9:17 am
Thom A - Saturday, October 20, 2018 5:37 AMsaravanatn - Saturday, October 20, 2018 3:24 AMdrew.allen - Friday, October 19, 2018 2:15 PMThis should be handled in the presentation layer, not the database layer.SQL Server is strongly typed. That means that you cannot mix string ('') and decimal data in the same column. This is why it's converting your '' to 0.
Drew
I am getting the answer directly(without any conversion) in sql server 2017.
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ad34673979ff21b187a4b490952990f6
When I tried in sql server 2008 I got conversion error .My question is different version of sql server behaves differently??That CASE won't return a conversion in SQL Server 2008 either; there is only one data type being returned a varchar(1), which is the value '' (the NULL will be implicitly cast to a varchar(1), as it doesn't have a defined datatype when you declared it).
I think you're misunderstanding how a CASE expression works. If you have the expression:
CASE WHEN A IS NULL THEN '' END
The above can only return 2 values. The first value is '', which is when A has a value of NULL. Otherwise NULL will be returned, as no expressions within the CASE evaluated to TRUE. To return the value of A is it isn't NULL you would use:CASE WHEN A IS NULL THEN '' ELSE A END
However, then you would likely be better off using ISNULL:ISNULL(A,'')
This, however, will still suffer conversion errors for what you have.
Thanks Thorn got it.
Saravanan
October 20, 2018 at 11:02 am
saravanatn - Saturday, October 20, 2018 9:16 AMJonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);insert into salary values(1,92.0111);
insert into salary values(2,null);select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salary
Always declare your lengths! That varchar needs one.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2018 at 11:17 am
Thom A - Saturday, October 20, 2018 11:02 AMsaravanatn - Saturday, October 20, 2018 9:16 AMJonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);insert into salary values(1,92.0111);
insert into salary values(2,null);select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salaryAlways declare your lengths! That varchar needs one.
It doesn't need a length.
In a CONVERT it will default to 30 characters so it's fine for converting these values.
When n is not specified in a data definition or variable declaration statement, the default length is 1.
When n is not specified when using the CAST and CONVERT functions, the default length is 30.
October 20, 2018 at 11:29 am
saravanatn - Saturday, October 20, 2018 9:16 AMJonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);insert into salary values(1,92.0111);
insert into salary values(2,null);select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salary
Hi Saravanatn,
Your table is already setup with type decimal for that column.
So all inner convert in my query does is convert the strings I have into the type in your table.
As your table is already decimal you can remove the inner convert.
October 20, 2018 at 12:24 pm
Jonathan AC Roberts - Saturday, October 20, 2018 11:17 AMThom A - Saturday, October 20, 2018 11:02 AMsaravanatn - Saturday, October 20, 2018 9:16 AMJonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);insert into salary values(1,92.0111);
insert into salary values(2,null);select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salaryAlways declare your lengths! That varchar needs one.
It doesn't need a length.
In a CONVERT it will default to 30 characters so it's fine for converting these values.Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1.
When n is not specified when using the CAST and CONVERT functions, the default length is 30.
No, it does. Not providing a length just encourages bad habits. There's never a good reason to not provide the length, just bad excuses.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2018 at 12:36 pm
Thom A - Saturday, October 20, 2018 12:24 PMJonathan AC Roberts - Saturday, October 20, 2018 11:17 AMThom A - Saturday, October 20, 2018 11:02 AMsaravanatn - Saturday, October 20, 2018 9:16 AMJonathan AC Roberts - Friday, October 19, 2018 3:38 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
SELECT COALESCE(convert(varchar,CONVERT(float,columnname)),'')
FROM (VALUES ('1'),('2.3'),(NULL)) a(columnname)Jonathan,
Why we need to use two convert when one convert is doing the job?
create table salary
(
id bigint,
emp_salary decimal(9,2)
);insert into salary values(1,92.0111);
insert into salary values(2,null);select id,case when emp_salary is null then '' else COALESCE(CONVERT(varchar,emp_salary),'') end
from salaryAlways declare your lengths! That varchar needs one.
It doesn't need a length.
In a CONVERT it will default to 30 characters so it's fine for converting these values.Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1.
When n is not specified when using the CAST and CONVERT functions, the default length is 30.No, it does. Not providing a length just encourages bad habits. There's never a good reason to not provide the length, just bad excuses.
You remind me of this:
October 20, 2018 at 6:41 pm
jcelko212 32090 - Friday, October 19, 2018 7:35 PMkeneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.You're confusing a SQL table with a spreadsheet. They are totally different things. We don't do display formatting in the database; that's passed to what we call a presentation layer in the host language. We also don't use the old Sybase convert () function; we've had the ANSI/ISO standard cast () for quite a few years now. Essentially, your problem is you haven't yet learned what a tiered architecture is or what a strongly typed language is.
Except that CAST doesn't do the things that CONVERT can do.
When it comes to databases, use whatever extensions are available.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2018 at 6:51 pm
keneangbu - Friday, October 19, 2018 2:03 PMThe data type of the column is decimal(9,2). I wanted an empty cell to be displayed when the output is NULL.
COALESCE(column, '') would display empty cell, shouldn't data type conversion be required.
However,COALESCE(CONVERT(float,columnname),'')displays 0.
When I try to execute COALESCE(columnname,'') an error 'converting data type varchar to numeric' is displaying.
Can anyone help me with is please? Thanks.
Ok... I just don't get it. If "the column is decimal(9,2)", why on this good Green Earth is everyone converting it to FLOAT as part of the code???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply