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

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

Code:

ALTER view [dbo].[vdvsalesorderline_cws] as

select
      a.sokey,a.trandate,ISNULL(a.chngorddate,a.TranDate) AS ChangeOrderDate,
      aj.costperfoot*c.qtyord
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

GO

** 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.
Comment
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
Commented:
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 ...

Author

Commented:
@ScottPletcher
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

Commented:
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