November 8, 2006 at 6:11 am
the subject header(above) should read 'in' instead of 'is'
Hi guys. Im using this select statement, when I execute it I get the following error msg:
Server: Msg 1035, Level 15, State 10, Line 2
Incorrect syntax near 'CAST', expected 'AS'.
this is the select statement:
SELECT IA.ITM_ID AS "Question Number",
CAST(IA.IAT_DATA) AS VARCHAR(25) AS "Subject",
(SUM(LOII.LII_MARKS) * 1.0) / COUNT(*) AS "Av No of correct answers"
CASE IA.IAT_DATA AS "Group"
WHEN 'Financial Reporting' THEN 1
WHEN 'Performance Management' THEN 2
WHEN 'Maths' THEN 3
END AS "Group"
FROM ITEM_ATTRIBUTES IA
INNER JOIN LEARNING_OBJECT_INSTANCE_ITEMS LOII ON IA.ITM_VERSION_NO = LOII.ITM_VERSION_NO
AND IA.ITM_ID = LOII.ITM_ID AND IA.ITM_SEQ_NO = LOII.ITM_SEQ_NO
WHERE IA.ATT_ID = 'Topic Area'
GROUP BY IA.ITM_ID, IA.IAT_DATA
ORDER BY "Group"
I've looked at books online and I cant see why the syntax is incorrect, anybody got any ideas...thanks in advance
November 8, 2006 at 6:24 am
It should be
CAST(IA.IAT_DATA AS VARCHAR(25)) AS Subject
November 8, 2006 at 6:36 am
thanks...I did what you suggested, but now I get a different error msg:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CASE'.
the current select statement:
SELECT IA.ITM_ID AS "Question Number",
CAST(IA.IAT_DATA AS VARCHAR(25)) AS Subject,
SUM(LOII.LII_MARKS) * 1.0 / COUNT(*) AS "Av No of correct answers"
CASE IA.IAT_DATA AS "Group"
WHEN 'Financial Reporting' THEN 1
WHEN 'Performance Management' THEN 2
WHEN 'Maths' THEN 3
END AS "Group"
FROM ITEM_ATTRIBUTES IA
INNER JOIN LEARNING_OBJECT_INSTANCE_ITEMS LOII ON IA.ITM_VERSION_NO = LOII.ITM_VERSION_NO
AND IA.ITM_ID = LOII.ITM_ID AND IA.ITM_SEQ_NO = LOII.ITM_SEQ_NO
WHERE IA.ATT_ID = 'Topic Area'
GROUP BY IA.ITM_ID, IA.IAT_DATA
ORDER BY "Group"
November 8, 2006 at 7:19 am
You're missing a comma and the end of the SUM line
...
SUM(LOII.LII_MARKS) * 1.0 / COUNT(*) AS "Av No of correct answers",
CASE IA.IAT_DATA AS "Group"
...
Far away is close at hand in the images of elsewhere.
Anon.
November 8, 2006 at 7:20 am
SELECT IA.ITM_ID AS "Question Number",
CAST(IA.IAT_DATA AS VARCHAR(25)) AS Subject,
SUM(LOII.LII_MARKS) * 1.0 / COUNT(*) AS "Av No of correct answers",
CASE IA.IAT_DATA
WHEN 'Financial Reporting' THEN 1
WHEN 'Performance Management' THEN 2
WHEN 'Maths' THEN 3
END AS "Group"
FROM ITEM_ATTRIBUTES IA
INNER JOIN LEARNING_OBJECT_INSTANCE_ITEMS LOII ON IA.ITM_VERSION_NO = LOII.ITM_VERSION_NO
AND IA.ITM_ID = LOII.ITM_ID AND IA.ITM_SEQ_NO = LOII.ITM_SEQ_NO
WHERE IA.ATT_ID = 'Topic Area'
GROUP BY IA.ITM_ID, IA.IAT_DATA
ORDER BY "Group"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply