Conversion failed when converting the varchar valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion failed when converting the varchar valu

Author

Topic

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-16 : 07:51:04

DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname +p.lastname as StudentName,a.assignmentname,cast((asg.pointsawarded+''/''+a.pointspossible )as int )as Marks
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(Marks) FOR assignmentname IN(' + @AssignmentList + '))p'

execute(@sql)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-16 : 08:05:33

You are using integer division. Try this

DECLARE @AssignmentList VARCHAR(3000),
	@Sql VARCHAR(MAX);

SET	@AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET	@Sql = 'SELECT	*
		from	(
				SELECT		p.FirstName + p.LastName AS StudentName,
						a.AssignmentName,
						CAST(100E * asg.PointsAwarded / a.PointsPossible) AS INT) AS Marks
				FROM		dbo.Person2 AS p
				INNER JOIN	dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
				INNER JOIN	dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
				LEFT JOIN	dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
				WHERE		cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
						AND cs.CourseRole = 2
			) AS t
		PIVOT	(
				SUM(Marks)
				FOR AssignmentName IN (' + @AssignmentList + ')
			) AS p';

EXEC	(@Sql);

N 56°04'39.26"
E 12°55'05.63"

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-16 : 08:15:00

it is not integer division. I need to display slash between two integers. the output should be 10/20

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-16 : 08:26:02

quote:
Originally posted by shriroshanraj

it is not integer division. I need to display slash between two integers. the output should be 10/20

If you want to display the slash, then the data type cannot be INT when you compute Marks column. Make it VARCHAR or another character type. But then, you cannot add them up in the PIVOT clause.

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 02:02:16

if i need slash there, how do i change the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:06:41

[code]
DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname +p.lastname as StudentName,a.assignmentname,cast((asg.pointsawarded+''/''+a.pointspossible )as varchar(20))as Marks
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(Marks) FOR assignmentname IN(' + @AssignmentList + '))p'

execute(@sql)
[/code]

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 02:11:03

i am getting error
Operand data type varchar is invalid for sum operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-18 : 02:12:57

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:17:29

quote:
Originally posted by shriroshanraj

i am getting error
Operand data type varchar is invalid for sum operator.

Oh Yes..You need to do cast before concatenation. Just didnt realize that when I posted.
Please use latest posted suggestion by Swepeso

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 02:21:04

only PointsAwarded is displaying , but i need to display PointsAwarded / PointsPossible under assignmentname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:30:45

quote:
Originally posted by shriroshanraj

only PointsAwarded is displaying , but i need to display PointsAwarded / PointsPossible under assignmentname

Does PointsPossible have valid values?

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 02:35:19

s PointsPossible has valid values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:57:18

Run this query first and post back result


SELECT	TOP 10	asg.PointsAwarded,a.PointsPossible ,
						CAST(asg.PointsAwarded AS VARCHAR(12)) + ''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks
				FROM		dbo.Person2 AS p
				INNER JOIN	dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
				INNER JOIN	dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
				LEFT JOIN	dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
				WHERE		cs.CoursesectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b'
						AND cs.CourseRole = 2

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 03:05:58

i changed like this, bcoz it shows error

CAST(asg.PointsAwarded AS VARCHAR(12)) + '/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

marks displays null value.

in my db PointsAwarded has null value. PointsPossible has value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 03:10:16

quote:
Originally posted by shriroshanraj

i changed like this, bcoz it shows error

CAST(asg.PointsAwarded AS VARCHAR(12)) + '/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

marks displays null value.

in my db PointsAwarded has null value. PointsPossible has value.


COALESCE(CAST(asg.PointsAwarded AS VARCHAR(12)) + '/','') + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 03:13:19

i changed like this

ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +'/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

Marks displays 0/12

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 03:14:57

If i use this

COALESCE(CAST(asg.PointsAwarded AS VARCHAR(12)) + '/','') + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

i get only PointsPossible value.

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-18 : 03:20:14

from the below query , i need to display the total number of pointswarded/total number of pointspossible under totalpoints.how to get the totalpoints.

the output should be

StudentName Totalpoints network testing createnew newtest
Raj Kumar 0/105 0/20 0/30 0/30 0/25

DECLARE @AssignmentList VARCHAR(3000),
@Sql VARCHAR(MAX);

SET @AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET @Sql = 'SELECT *
from (
SELECT p.FirstName + p.LastName AS StudentName,
a.AssignmentName,
ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks
FROM dbo.Person2 AS p
INNER JOIN dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID
INNER JOIN dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
LEFT JOIN dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
WHERE cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
AND cs.CourseRole = 2
) AS t
PIVOT (
MAX(Marks)
FOR AssignmentName IN (' + @AssignmentList + ')
) AS p';

EXEC (@Sql);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 04:31:06

[code]
DECLARE @AssignmentList VARCHAR(3000),
@Sql VARCHAR(MAX);

SET @AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET @Sql = 'SELECT *
from (
SELECT p.FirstName + p.LastName AS StudentName,
a.AssignmentName,
ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks,
ISNULL(CAST(SUM(asg.PointsAwarded) OVER (PARTITION BY p.FirstName,p.LastName) AS VARCHAR(12)) ,0) +''/'' + CAST(SUM(a.PointsPossible) OVER (PARTITION BY p.FirstName,p.LastName) AS VARCHAR(12)) AS TotalMarks
FROM dbo.Person2 AS p
INNER JOIN dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID
INNER JOIN dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
LEFT JOIN dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
WHERE cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
AND cs.CourseRole = 2
) AS t
PIVOT (
MAX(Marks)
FOR AssignmentName IN (' + @AssignmentList + ')
) AS p';

EXEC (@Sql);
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources