CODEWITHSUNDEEP
sqlsql-serverview
gtallen1187
gtallen1187

Reputation: 23

SQL - Error when running view, but not when running query inside view

can anyone explain how a query functions differently once it is placed inside a view? More specifically, i have noticed that often times i will get datatype conversion errors when i run the view, but not when i run the isolated query inside of the view. For example:

select 
 cast(id as int(11)) as 'UserID',
 name,
 dob
from myTable

The query runs perfectly fine. however, after i save this query as a view:

create view myView as (
select 
 cast(id as int(11)) as 'UserID',
 name,
 dob
from myTable
)

and then try and run the view by select * from myView , often times i will get conversion errors like "arithmetic overflow converting datatype varchar to numeric". Could someone explain what changes about the query when it is stored as a view that causes this error?

Upvotes: 1

Views: 2432

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This probably has to do with the order of the data being returned. If you returned all the rows, your regular query would probably encounter the error. You apparently have bad values in the id. Onw way to fix this is:

select (case when isnumeric(id) and id not like '%.%' then cast(id as int(11)) end) as UserID,
       name,
       dob
from myTable;

The "right" way, of course, is to store numeric data as numbers rather than as strings.

Upvotes: 1

Related Questions