Avatar of ClowWater
ClowWaterFlag for United States of America

asked on 

TSQL : Select/Join works fine until I save it as a view?

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')
            on b.itemkey=d.itemkey
      on a.sokey=b.sokey


** 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.
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ClowWater
Flag of United States of America image


Your idea worked!  I put a case statment in front of the cast() and all is well.  But seeing your code prompts me to ask one more question...
is it better to use left(d.itemid,1) in ('1','2','3','4','5','6','7','8','0') or left(d.itemid,1) like '[0-8]'
It's actually much better to use:

d.itemid like '[0-8]%'

because, at least in theory, that allows an index on itemid to be considered (although that's such a wide range of chars the index likely wouldn't be chosen anyway, it's still a good general principal -- avoid using functions on columns if you can).
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo