March 17, 2011 at 7:51 am
if I change the view:
SELECT DISTINCT a.[FULL NAME] AS EmployeeName, a.CC, a.CCNAME, cq.Score, dbo.SedationFeedBack.[Submit Time], dbo.SedationFeedBack.ID
FROM Annual_Edu_2006.dbo.HREMP AS a INNER JOIN
dbo.[Module Log] AS cm ON a.[FULL NAME] = cm.[Employee Name] AND a.DOB = cm.DOB INNER JOIN
dbo.[Quiz Log] AS cq ON cm.[Employee Name] = cq.[Employee Name] AND cm.DOB = cq.DOB INNER JOIN
dbo.SedationFeedBack ON cq.[Employee Name] = dbo.SedationFeedBack.[Employee Name] AND cq.DOB = dbo.SedationFeedBack.DOB
WHERE (cm.[Module Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (CAST(SUBSTRING(cq.Score, 1,
LEN(cq.Score) - 1) AS int) >= 80) OR
(cm.[Module Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (CAST(SUBSTRING(cq.Score, 1,
LEN(cq.Score) - 1) AS int) >= 80)
to
SELECT DISTINCT a.[FULL NAME] AS EmployeeName, a.CC, a.CCNAME, cq.Score, dbo.SedationFeedBack.[Submit Time], dbo.SedationFeedBack.ID
FROM Annual_Edu_2006.dbo.HREMP AS a INNER JOIN
dbo.[Module Log] AS cm ON a.[FULL NAME] = cm.[Employee Name] AND a.DOB = cm.DOB INNER JOIN
dbo.[Quiz Log] AS cq ON cm.[Employee Name] = cq.[Employee Name] AND cm.DOB = cq.DOB INNER JOIN
dbo.SedationFeedBack ON cq.[Employee Name] = dbo.SedationFeedBack.[Employee Name] AND cq.DOB = dbo.SedationFeedBack.DOB
WHERE (cm.[Module Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (CAST(SUBSTRING(cq.Score, 1,
LEN(cq.Score) - 1) AS int) >= 80 AND (cq.Score <> 'waiver') [/u]) OR
(cm.[Module Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (cq.[Quiz Name] = 'Sedation') AND (CAST(SUBSTRING(cq.Score, 1,
LEN(cq.Score) - 1) AS int) >= 80 AND (cq.Score <> 'waiver') )
it will give me conversion error. I think because it cast to the cq score to int already. but If I put the cq.score<>'waiver' before cast. It still give me the same error. Thanks.
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Waive' to a column of data type int.
if I added one condition like
March 17, 2011 at 1:41 pm
did you paste the exact error? It states something about converting 'Waive' to an int. You have 'Waiver' in your example.
please double check...
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 18, 2011 at 6:13 am
Thank you for catching the mistake. But the in the database. The quiz log have field score. The data should be 80% or Waived 10% something like that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply