Avatar of ClowWater
ClowWater
Flag 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.

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

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ClowWater

ASKER
@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 Pletcher

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).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck