I have this view that I'm building (see below)...and in SSMS I can just highlight and run the 'select' part without errors, but if I execute this alter statement and then run a simple select of the view it errors out. The error has to do with the "long" join line.
ALTER view [dbo].[vdvsalesorderline_cws] as
a.sokey,a.trandate,ISNULL(a.chngorddate,a.TranDate) AS ChangeOrderDate,
from tsosalesorder a
left join tsosoline b
left join timitem d
left join tLCcosts_cws aj on cast(substring(d.itemid,2,2) as int)=cast(aj.pipesize as int) and substring(d.itemid,8,1)='8' and d.userfld2='Y' and left(d.itemid,1) in ('1','2','3','4','5','6','7','8','0')
** so if I highlight the 'select' code it runs just fine. If I execute this code and it modifies the view and run a simple "select * from vdvsalesorderline_cws" it errors out with the error stating it cannot convert "ff" to an int...which I understand and that is why I had to put so many "and" statements on the join so that it wouldn't try to convert the rows that may have bad "pipesize" data.
I guess my question is why does the select alone work but it doesn't work as a view. Does the execution act differently when running a select from a view?
This one is really puzzling me...any insight is GREATLY appreciated.