Please help on these error messages

Question

Post reply

Please help on these error messages

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
September 3, 2013 at 6:01 am

#395408

Please help, I am getting these error messages.
Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyword 'AS'.
The query is below:
DECLARE @Results TABLE
(
studyCode nvarchar(40), StudyDirector nvarchar(244), activityCode nvarchar(40), PhaseType nvarchar(244), ActivityOwner nvarchar(244),TQSDComment nvarchar(2000),TQPIComment nvarchar(2000)
);
INSERT @Results (studyCode,StudyDirector,activityCode,ActivityOwner)
SELECTS.studyCode AS studyCode,
SOP.operatorName AS StudyDirector,
A.activityCode AS activityCode,
AOP.operatorName AS ActivityOwner
FROM Activities A
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0
--Filter on AF with extractName 'TrialEvaluation' AND the AFValue 'major issue : trial not valid/cancelled ' (But use the AFV code instead of the name)
LEFT JOIN ActivitiesAdditionalFieldsValues AS AAFV WITH(NOLOCK) ON A.activityIncId=AAFV.activityIncId AND A.activitySqlId=AAFV.activitySqlId AND AAFV.isDeleted=0x0
INNER JOIN ActivitiesAdditionalFields AS AAF WITH(NOLOCK) ON AAFV.activityAdditionalFieldIncId=AAF.activityAdditionalFieldIncId AND AAFV.activityAdditionalFieldValueSqlId=AAF.activityAdditionalFieldSqlId AND AAF.isDeleted=0x0
AND AAF.extractName = 'TrialEvaluation'
INNER JOIN CboValues CBOV ON AAFV.cboRecordIncId = CBOV.cboValueIncId AND AAFV.cboRecordSqlId = CBOV.cboValueSqlId AND CBOV.isDeleted=0x0
AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled '
LEFT JOIN Operators AS AOP ON A.todoBySqlId = AOP.operatorSqlId AND A.todoByIncId = AOP.operatorIncId
LEFT JOIN Operators AS SOP ON S.directedBySqlId = SOP.operatorSqlId AND S.directedByIncId = SOP.operatorCategoryIncId
WHERE A.isDeleted=0x0
AND TOS.typeOfStudyCode = 'EAS-01'
AND TOA.typeOfActivityCode = 'EAS-1'
AND AC.activityCategoryCode = 'EAS-1F'
----AND (A.activityStartDate < DateAdd(DAY,1,@ToDate) AND A.activityEndDate >= @FromDate)
ORDER BY S.studyCode, A.activityCode
UPDATE @Results SET PhaseType = CASE ISNULL(CATCA.activityCategoryName, '') WHEN '' THEN TOCA.typeOfActivityName
ELSE TOCA.typeOfActivityName + ' ; ' + CATCA.activityCategoryName END AS PhaseType
FROM Activities AS CA
LEFT JOIN ActivitiesCategories AS CATCA ON CATCA.activityCategorySqlId=CA.activityCategorySqlId AND CATCA.activityCategoryIncId=CA.activityCategoryIncId AND CATCA.isDeleted=0x0
LEFT JOIN TypesOfActivities AS TOCA ON CA.typeOfActivitySqlId=TOCA.typeOfActivitySqlId AND CA.typeOfActivityIncId=TOCA.typeOfActivityIncId AND TOCA.isDeleted=0x0
ON A.activitySqlId=CA.fatherActivitySqlId AND A.activityIncId=CA.fatherActivityIncId AND CA.isDeleted=0x0
AND TOCA.typeOfActivityName Like '%Running%'
UPDATE @Results SET TQSDComment = SDAAFV.txtValue AS TQSDComment
FROM ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0
ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0
AND SDAAF.extractName = 'TQSDComment'
UPDATE @Results SET TQSDComment = PIAAFV.txtValue AS TQPIComment
FROM ActivitiesAdditionalFieldsValues AS PIAAFV
LEFT JOIN ActivitiesAdditionalFields AS PIAAF ON PIAAFV.activityAdditionalFieldIncId=PIAAF.activityAdditionalFieldIncId AND PIAAFV.activityAdditionalFieldValueSqlId=PIAAF.activityAdditionalFieldSqlId AND PIAAF.isDeleted=0x0
ON A.activityIncId=PIAAFV.activityIncId AND A.activitySqlId=PIAAFV.activitySqlId AND PIAAFV.isDeleted=0x0
AND PIAAF.extractName = 'TQPIComment'
SELECT * FROM @Results;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlnaive SSCoach Points: 17437 More actions · Answer 1

It's pretty simple. Remove the alias "AS" from each of the update statement.

UPDATE @Results

SET PhaseType = CASE ...END AS PhaseType

UPDATE @Results

SET TQSDComment = SDAAFV.txtValue AS TQSDComment

UPDATE @Results

SET TQSDComment = PIAAFV.txtValue AS TQPIComment

Try this... 🙂

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

You can not give alias names to a column being updated inside an update statement. Remove the text "AS PhaseType", "AS SDAAFV" and "AS TQPIComment" from the update statements.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Andreas P. Williams SSCrazy Points: 2487 More actions · Answer 3

The error is in the three updates: You can't use an alias for the expression on the right-hand side of the '='.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Unrelated to the errors, but be very careful using a table variable. The performance impact is often not acceptable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

What do you think this update statement should do?

UPDATE @Results SET

TQSDComment = SDAAFV.txtValue --AS TQSDComment

FROM ActivitiesAdditionalFieldsValues AS SDAAFV

LEFT JOIN ActivitiesAdditionalFields AS SDAAF

ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId

AND DAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId

AND SDAAF.isDeleted=0x0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden