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

ClowWater used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
The order in which SQL evaluates conditions can vary, based upon what the optimizer thinks is best.

AFAIK, the only way to force a sequential processing of conditions is to use a CASE statement -- by definition, CASE statement always evaluate sequentially, from first to last.

For example:

left join tLCcosts_cws aj on
    case when substring(d.itemid, 2, 2) like '[123456780][0-9]'
        then cast(substring(d.itemid,2,2) as int)
        else -1 end
    = cast(aj.pipesize as int) and substring(d.itemid,8,1)='8' and d.userfld2='Y'
on ...


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]'
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial