Error message - data type issue - turn query into view

Question

Post reply

Error message - data type issue - turn query into view

rk1980factor

SSCommitted

Points: 1681
More actions
May 10, 2016 at 10:35 am

#316440

When I run this view query:
//
SELECT [student#]
,[class_position]
,case when [class_position] > 0 then [Class_Rank] else [Class_Seq] end as [List]
FROM School
//
It gives me error message:Msg 244, Level 16, State 2, Line 4:The conversion of the varchar value '60011' overflowed an INT2 column. Use a larger integer column.
Only thing I found out was data type for column [Class_Rank] is (varchar(5),null) and column [Class_Seql] is (smallint,not null)
My Question is : How can I run this query without that error message and how can i turn this query into views?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Try this:

CREATE VIEW ViewName

AS

SELECT [student#]

,[class_position]

,case when [class_position] > 0 then CONVERT(int, [Class_Rank]) else CONVERT(int, [Class_Seq]) end as [List]

FROM School

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

rk1980factor SSCommitted Points: 1681 More actions · Answer 2

I tried that and now it gives me this error message:

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'PR001' to data type int.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

rk1980factor (5/10/2016)
I tried that and now it gives me this error message:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value 'PR001' to data type int.

Guess that next suggestion should be converting to varchar

😎

CREATE VIEW ViewName

AS

SELECT [student#]

,[class_position]

,case when [class_position] > 0 then CONVERT(VARCHAR(12), [Class_Rank]) else CONVERT(VARCHAR(12), [Class_Seq]) end as [List]

FROM School

rk1980factor SSCommitted Points: 1681 More actions · Answer 4

That convert worked, thanks

but i tried to add another case statement:

SELECT [student#]

,[class_position]

,case when [class_position] > 0 then [Assembly_Rank] else [Assembly_Seq] end as [Line_Class]

,case when [class_position] > 0 then CONVERT(VARCHAR(12), [Class_Rank]) else CONVERT(VARCHAR(12), [Class_Seq]) end as [List]

FROM School

Now when i run as a query it runs fine, but when i try to create views

like

Create View Viewname

as

(and than include the full select statement)

it gives me following error message:

Msg 4506, Level 16, State 1, Procedure viewname, Line 5

Column names in each view or function must be unique. Column name 'class_position' in view or function 'viewname' is specified more than once.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

rk1980factor (5/10/2016)
That convert worked, thanks
but i tried to add another case statement:
SELECT [student#]
,[class_position]
,case when [class_position] > 0 then [Assembly_Rank] else [Assembly_Seq] end as [Line_Class]
,case when [class_position] > 0 then CONVERT(VARCHAR(12), [Class_Rank]) else CONVERT(VARCHAR(12), [Class_Seq]) end as [List]
FROM School
Now when i run as a query it runs fine, but when i try to create views
like
Create View Viewname
as
(and than include the full select statement)
it gives me following error message:
Msg 4506, Level 16, State 1, Procedure viewname, Line 5
Column names in each view or function must be unique. Column name 'class_position' in view or function 'viewname' is specified more than once.

Since you didn't post the SQL for the actual view all I can guess is that you have the same column name in the select list even if it is from different tables. You need to alias the columns so they are each unique.