• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Combining 2 numerical results without doing a sum MS SQL 2005

I want to combine 2 numerical results from different columns into one result that i save in a third column,

the stament i have at the moment is:

update myTable set myCol = 2.55 where something = something

i want to do something like this

update myTable set myCol = 2.55 + / + 19 where something = something
 and get this result
2.55/19
when i try that above, i get an error
0
QuinnDester
Asked:
QuinnDester
  • 7
  • 5
  • 2
2 Solutions
 
mayank_joshiCommented:
try:-

update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/ '+ cast( 19 as NVARCHAR(50))  where something = something

Open in new window

0
 
mayank_joshiCommented:
you may also do:-

update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/ '+ cast(col2 as NVARCHAR(50))  where something = something

Open in new window


where col1 and col2 are field names.
0
 
mayank_joshiCommented:
sorry i wrote an extra empty space in '/ '
Correct query:-

update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/'+ cast( 19 as NVARCHAR(50))  where something = something

update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/'+ cast(col2 as NVARCHAR(50))  where something = something

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Bhavesh ShahLead AnalysistCommented:
Hi,

if its hard code value then you no need to do cast
this will do

update myTable set myCol = '2.55/19'
0
 
QuinnDesterAuthor Commented:
it is complicated slightly by using dynamic sql, what you have given me works, but putting it into dynamic sql it isnt saving anything after the col1, but is saving the col1 ??


here is what i am using

'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0
 
mayank_joshiCommented:
why are you using double quotes(") ?
0
 
QuinnDesterAuthor Commented:
the reason for using dynaimc sql, is the col name on one of the tables is decided dynamicaly, there is no way around that that i know of.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

can you post your full code?
0
 
QuinnDesterAuthor Commented:
double quots are so i get a single quote when the dynamic sql is executed
0
 
QuinnDesterAuthor Commented:
that is the full statment conserned, but if you think it will help the full sp is below
USE [MainDB]
GO
/****** Object:  StoredProcedure [dbo].[UpdateSystemData]    Script Date: 03/14/2011 09:40:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[UpdateSystemData](
@name varchar (Max),
@status varchar (Max), 
@player varchar (Max),
@alliance varchar (Max),
@Position int, 
@Galaxy int,
@System int,
@HasMoon bit, 
@date datetime, 
@Coordinates nvarchar (50)
 )
AS
BEGIN
if @player not like 'Joshua Calvert'
begin
 update planets set invalid = 0, coordinates = @Coordinates, timelastupdated = @date, [name] = @name, player=@player, alliance=@alliance, hasmoon=@HasMoon 
 where galaxy=@Galaxy and [system]=@System  and position=@Position

declare @col varchar (max)
declare @sql nchar (500)
declare @thishour int
declare @lastupdated datetime
declare @updatedcount int
declare @avgstatus dec(12,2)

set @thishour = (select top 1 DATEPART(hour, @date))
set @lastupdated = (select lastupdated from activity_history where Coordinates = @Coordinates)
set @col = (select hour_words from convert_hour where hour_numbers = @thishour)


if @lastupdated <= dateadd(hour, -1, getdate()) or @lastupdated is null
begin
	set @updatedcount = (select updatedcount + 1 from activity_history where  Coordinates = @Coordinates)
update activity_history set updatedcount = @updatedcount, LastUpdated = getdate() where  Coordinates = @Coordinates
	set @sql = 'update activity_history set '+ 
	cast(@col as varchar (max))+ ' = '+ cast(@col as varchar (max))+' + '+ cast(@status as varchar (max)) +' 
where Coordinates =  '''+ cast(@Coordinates as varchar (max)) +'''declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' + 
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''


exec (@sql)

--declare @sql1 nchar (500)
--set @sql1 = 'update planets set [status] = (select dbo.avg1('+cast(@thishour as varchar (max))+',updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')'
--exec (@sql1)
end
else
begin
set @sql = 'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' + 
cast(@col as varchar (max))+' ,updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = @avgstatus where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''


exec (@sql)

end

end
END

Open in new window

0
 
mayank_joshiCommented:
this should work:-

update planets set [status] = @avgstatus where Coordinates = cast(@Coordinates  as varchar (max))

Open in new window

0
 
QuinnDesterAuthor Commented:
that part in the in the else statment works fine, its the if statment where i have tried to add the extra info to that statment where it not working...
0
 
QuinnDesterAuthor Commented:
got it...

Thanks for your help

this works, though the only difference is a space in a place where i though a space wouldnt matter, obviously it does :)

'update planets set [status] = cast(@avgstatus as varchar (max)) +''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0
 
QuinnDesterAuthor Commented:
my solution fixed the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now