Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a view with Declare Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:01 PM
Points: 8, Visits: 42
I am trying to create a view with the below query.
it should be a view as data in other tables will be updated and I want to refer to this often. Or as a last resort schedule the creation of this table over night some how?

DECLARE @@string1 varchar (20) = ' Number='
DECLARE @@string2 varchar (20) = ' And Id='

SELECT f.linkid, f.docname, f.title, dc.catname, t.template, r.extlink ,f.libid

,substring(extlink, charindex(@@string1, extlink)+len(@@string1),
charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink)) as ID

from AL_TEMPLATE t
INNER JOIN AL_RELATION r ON t.tempID=r.tempid
INNER JOIN AL_RELATED_FILE rf ON r.relID=rf.relid
INNER JOIN al_file f ON rf.linkid=f.linkID
INNER JOIN al_filecats fc ON f.linkid=fc.linkid
INNER JOIN AL_DOCCAT dc ON fc.catid=dc.catID

where charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink) > 0

Thanks
Post #1476349
Posted Tuesday, July 23, 2013 1:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
A view can only contain a single select statement, no other statements are allowed.

Try an in-line table-valued function with those two variables as parameters to the function.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1476390
Posted Tuesday, July 23, 2013 1:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:01 PM
Points: 8, Visits: 42
I'm pretty sure you cant use variables in views, I have decided to take them out which isn't the end of the world.

I haven't worked out how to create a table on a scheduled task yet but i think the above method is better for this purpose.


Thanks
Post #1476392
Posted Tuesday, July 23, 2013 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:01 PM
Points: 8, Visits: 42
sorry GilaMonster, didn't see your response,

um - ok I'll have a look at that thanks!
Post #1476394
Posted Tuesday, July 23, 2013 7:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
bawinkley (7/23/2013)
I'm pretty sure you cant use variables in views, I have decided to take them out which isn't the end of the world.

I haven't worked out how to create a table on a scheduled task yet but i think the above method is better for this purpose.


Thanks

As Gail already suggested you can use inline table valued function and can pass those two parameteres in that..........



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1476558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse