Q&A for Tridion developers and administrators
SELECT SUBSTRING(p.Title, (PATINDEX('%("ORA"|"TNS")-[0-9][0-9][0-9][0-9][0-9]%',p.[Title])),9), p.Id, Answered = CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN 'Yes' ELSE 'No' END, p.AnswerCount, p.ViewCount, p.CommentCount, UpVote = SUM(CASE WHEN vt.name = 'UpMod' THEN 1 ELSE 0 END), Favorite = SUM(CASE WHEN vt.name = 'Favorite' THEN 1 ELSE 0 END), OtherVote = SUM(CASE WHEN vt.name NOT IN ('UpMod', 'Favorite') THEN 1 ELSE 0 END) FROM Posts p INNER JOIN Votes v ON p.Id = v.PostId INNER JOIN VoteTypes vt ON v.VoteTypeId = vt.Id WHERE vt.Name NOT IN ('DownMod', 'Offensive', 'Deletion', 'Spam') AND p.Tags LIKE '%oracle%' AND (p.title LIKE '%ORA-%' OR p.title LIKE '%TNS-%') GROUP BY SUBSTRING(p.Title, (PATINDEX('%("ORA"|"TNS")-[0-9][0-9][0-9][0-9][0-9]%',p.[Title])),9), p.Id, p.AcceptedAnswerId, p.ViewCount, p.AnswerCount, p.CommentCount ORDER BY ViewCount DESC;