March 23, 2022 at 2:18 pm
use Joedata
go
DECLARE @CursorID INT;
DECLARE @ViewSite varchar(12);
DECLARE CUR_Loop CURSOR FAST_FORWARD FOR
SELECT distinct IndexSite
,[IndexWeekOf]
,[IndexMonth]
-- ,[IndexSite]
,[IndexDepartment]
,[IndexSpecialty]
,[IndexSkillNumber]
,[IndexSkillset]
,[id]
,[CalcCallsOffered]
,[CalcAdjOffered]
,[CalcAdjAban]
,[CalcAbanPercent]
,[CalcASA]
,[CalcTalk]
,[CalcHold]
,[CalcACW]
,[CalcAHT]
,[CalcOccupancy]
,[WaitTime]
,[AverageAbandonTime]
,[CalcPercentAns]
,[CalcAdjPercentAns]
FROM [Joedata].[dbo].[tblUSHC_SkillDailyCalc]
ORDER BY ID;
OPEN CUR_Loop
FETCH NEXT FROM CUR_Loop INTO @CursorID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ViewSite = 'v' + CursorID.IndexSite;
CREATE VIEW ViewSite AS
select distinct tblUSHC_SkillDailyCalc
where tblUSHC_SkillDailyCalc.id = @CursorID
FETCH NEXT FROM CUR_Loop INTO @CursorID
END
CLOSE CUR_Loop
DEALLOCATE CUR_Loop
GO
does not allow CursorID.IndexSite; I don't see whats wrong
March 23, 2022 at 2:55 pm
CursorID.IndexSite is incorrect syntax
Your select statement 22 columns, and you are fetching into a single variable.
It also appears that you are attempting to create a series of views named "v" + the name of a site.
If so, this is not going to work as written. You will need to use dynamic SQL.
This syntax appears to be incorrect:
select distinct tblUSHC_SkillDailyCalc
where tblUSHC_SkillDailyCalc.id = @CursorID
Is tblUSHC_SkillDailyCalc a table?
Is "IndexSite" an integer?
This code will generate the T-SQL for you. You can copy and paste the results into a new query window and execute it.
SELECT DISTINCT
'CREATE VIEW [dbo].v' + CONVERT(varchar(10), IndexSite) + '
SELECT DISTINCT *
FROM [Joedata].[dbo].[tblUSHC_SkillDailyCalc]
WHERE ID = ' + CONVERT(varchar(12), ID) + '
ORDER BY ID;
GO'
FROM tblUSHC_SkillDailyCalc
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 23, 2022 at 2:56 pm
What you're trying to do doesn't make much sense to me, but you need to build "dynamic sql" for the "create view" statement you are trying to generate and implement.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 23, 2022 at 3:00 pm
CursorID is an integer, not a collection.
You need to declare a variable for each field that the cursor returns
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply