SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Concatenate Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

easy_goer
Starting Member

21 Posts

Posted - 11/27/2013 :  11:37:01  Show Profile  Reply with Quote
Hello,

I am attempting the run the following script..

select 'Active|'+column1+'|'+column2
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

but, I receive the following error.

"Error converting data type varchar to float"

Column1 is a FLOAT column and Column2 is a INT column. Active is just text that I'm inserting.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  11:41:42  Show Profile  Reply with Quote
you need to cast them to varchar before you do concatanation


select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/27/2013 :  11:50:37  Show Profile  Reply with Quote
Thanks. When I try that the results pulled from Column1 are not displaying correctly. So, instead of displaying with the number I expect (e.g. 1000000, 1000001, 1000002, etc.) it's displaying like this..

1.94744e+006, 1.94745e+006, etc. Does this make sense?

Thanks!


quote:
Originally posted by visakh16

you need to cast them to varchar before you do concatanation


select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  12:24:25  Show Profile  Reply with Quote
quote:
Originally posted by easy_goer

Thanks. When I try that the results pulled from Column1 are not displaying correctly. So, instead of displaying with the number I expect (e.g. 1000000, 1000001, 1000002, etc.) it's displaying like this..

1.94744e+006, 1.94745e+006, etc. Does this make sense?

Thanks!


quote:
Originally posted by visakh16

you need to cast them to varchar before you do concatanation


select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





thats because its declared as float
what about this?


select 'Active|'+ CAST(CAST(column1 AS decimal(38,18)) AS varchar(38)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 12/03/2013 :  13:04:54  Show Profile  Reply with Quote
That got me really close. What I ended up doing is changing the decimal portion from decimal(38,18) to decimal(38,0). The reason is that I just need the whole number. I don't need a decimal point or anything after the decimal point. Is there any risk in using decimal(38,0)? So far, it seems to work great.

Thanks!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 12/03/2013 :  13:25:24  Show Profile  Reply with Quote
nothing much except the value after decimal point will be truncated :)

select CONVERT(numeric(38,18),12345678901234567890.123456789012345678)
select CONVERT(numeric(38,0),12345678901234567890.123456789012345678)

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/04/2013 :  00:15:36  Show Profile  Reply with Quote
quote:
Originally posted by easy_goer

That got me really close. What I ended up doing is changing the decimal portion from decimal(38,18) to decimal(38,0). The reason is that I just need the whole number. I don't need a decimal point or anything after the decimal point. Is there any risk in using decimal(38,0)? So far, it seems to work great.

Thanks!


why not use int then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000