June 25, 2011 at 3:58 pm
I am writing a query for a view which creates some tables from inside the view and then use them table in the query and finally drop them at the end. I keep getting an error saying "Incorrect syntax near the keyword 'Create'.
Here is my query:
Create table statuss(surveyInstanceID int, answered int, outOf int, [percent] numeric(15,2), isActive bit, designation varchar(50), siteOrdinal bigint)
Insert Into statuss(surveyInstanceID, answered, outOf, [percent], isActive, designation, siteOrdinal)
SELECT surveyInstanceID, answered = sum(answered), outOf = count(*), [percent] = cast(
sum(answered)* 100.0 / count(*) AS numeric(15, 2)), isActive,
designation, siteOrdinal
FROM (SELECT si.surveyInstanceID, cqr.questionID, answered = CASE WHEN sf.answerNumeric IS NULL AND (sf.answerText IS NULL OR
rtrim(sf.answerText) = '') THEN 0 ELSE 1 END, ordinal = rank() OVER (partition BY sf.surveyInstanceID, sf.questionID
ORDER BY sf.answeredDate DESC), si.isActive, designation = sd.name, siteOrdinal = row_number() OVER (partition BY si.forSite_OrganizationID,
sf.questionID
ORDER BY si.dateStarted, sf.answeredDate DESC)
FROM svCategoryQuestionRisk cqr JOIN
surveyInstance si ON cqr.surveyVersionID = si.surveyVersionID JOIN
surveyFilled sf ON si.surveyInstanceID = sf.surveyInstanceID AND cqr.questionID = sf.questionID JOIN
surveyCategoryHierarchy ch ON ch.child_categoryid = cqr.categoryId AND ch.surveyVersionID = cqr.surveyVersionID JOIN
surveyDesignation sd ON si.surveyDesignationID = sd.surveyDesignationID) A
WHERE ordinal = 1
GROUP BY surveyInstanceID, isActive, designation, siteOrdinal
Create Table ccRisk(ID int, type varchar(8), surveyInstanceID int, surveyVersionID int)
Insert Into ccRisk(ID, type, surveyInstanceID, surveyVersionID)
Select cc.ID, cc.type, cc.surveyInstanceID, cc.surveyVersionID
from categoryColsQuestionLORALL cc, statuss ss
Where cc.type = 'Category' and cc.surveyInstanceID = ss.surveyInstanceID
CREATE TABLE theseQuestions(questionID int)
Insert Into
theseQuestions(questionID)
select distinct sf.questionID
from surveyFilled sf
inner join svCategoryQuestionRisk svr on sf.questionID = svr.questionID
inner join ccRisk cc on svr.categoryID = cc.ID
inner join vw_surveyCategoryHierarchyFlat fl on cc.ID = fl.categoryID
and ( fl.fullPath like '%>67>%' or fl.fullPath like '%>67')
and cc.surveyInstanceID = sf.surveyInstanceID
and fl.surveyVersionID = cc.surveyVersionID
select ss.*,
(select thisAnswer = dbo.LowerArgument(100, cast(ss.[percent] + ((24 - dbo.LowerArgument
(
sum(
CASE WHEN
sff.answerNumeric IS NULL AND (sff.answerText IS NULL OR
rtrim(sff.answerText) = '')
THEN 0 ELSE 1 END), 24)) * 100.0 / ss.outOf) AS numeric(15, 2)))
from surveyFilled sff
INNER JOIN theseQuestions tq ON sff.questionID = tq.questionID
where sff.surveyInstanceID = ss.surveyInstanceID) AS newPercent
FROM statuss ss
DROP TABLE theseQuestions
DROP TABLE ccRisk
DROP TABLE statuss
This query runs perfect when I run it but cannot save it in a view. So if somebody can help m eout here.
P.S.: I tried using temp tables as I drop them at the end, but then temp tables are not allowed inside a view.
Thanks,
Ratandeep Gupta
June 25, 2011 at 5:09 pm
The only thing a VIEW can contain is a SELECT statement. What you want is a stored procedure. Or, if you change your concrete tables to table variables you could use a Multi-statement table-valued function for this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 2:35 pm
Hello, Thanks for the reply. Can you give me an example of how to use Multi-statement table valued function.
Thanks,
Ratan
June 27, 2011 at 2:40 pm
seftest_09 (6/27/2011)
Hello, Thanks for the reply. Can you give me an example of how to use Multi-statement table valued function.Thanks,
Ratan
The function ufnGetContactInformation in this Books Online article is a mTVF (Multi-statement table valued function):
http://msdn.microsoft.com/en-us/library/ms191165.aspx
Note that you will have to convert the temp tables in your example to table variables to put your code in a mTVF.
Also note that mTVFs can be terribly slow when used as concrete tables in joins...a generally better performing option is a stored procedure. You can redirect the results of a stored proc into a temp table using the INSERT...EXEC construct, and then join to the temp table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply