troubleshooting Question

Need to convert complex Access query containing IIF statements into SQL Server view

Avatar of LSE_IT_Training
LSE_IT_Training asked on
Microsoft AccessMicrosoft SQL Server
3 Comments1 Solution490 ViewsLast Modified:
Hi,

I need to convert the Access query below into a SQL Server view. Most of the problems I've encountered seem to relate to converting the IIF into CASE WHEN statements and with datatypes. I have included my latest effort below - the last error message states "Conversion failed when converting the varchar value '<a href="http://ittraining' to data type int.
*********Original Access query*************
SELECT [Course details].ShowBookingOnWeb, [Course details].CourseID, [Courses Title].WebWordApp, [Courses Title].WebWordLevel, [Courses Title].CourseTitleID, [Courses Title].CourseTitle, [Courses Title].CourseType, IIf([coursedate] Is Null,"Date to be confirmed",[coursedate]) AS [Date], [Courses Title].CourseDescription, [Courses Title].CourseRequirements, [Course details].CourseDate, [Course details].CourseTime, IIf((Count([studentIDcard])<15),"<a href='http://ittraining.lse.ac.uk/students/booking.asp?courseID=" & [Course Details.courseid] & "')>Reserve a place</a>","Drop-in places only") AS Placesavailable, StudentInformation.Studentattendingstatus
FROM [Courses Title] INNER JOIN ([Course details] INNER JOIN StudentInformation ON [Course details].CourseID = StudentInformation.CourseID) ON [Courses Title].CourseTitleID = [Course details].CourseTitleID
GROUP BY [Course details].ShowBookingOnWeb, [Course details].CourseID, [Courses Title].WebWordApp, [Courses Title].WebWordLevel, [Courses Title].CourseTitleID, [Courses Title].CourseTitle, [Courses Title].CourseType, IIf([coursedate] Is Null,"Date to be confirmed",[coursedate]), [Courses Title].CourseDescription, [Courses Title].CourseRequirements, [Course details].CourseDate, [Course details].CourseTime, StudentInformation.Studentattendingstatus
HAVING ((([Course details].ShowBookingOnWeb)=0) AND (([Course details].CourseDate)>=Now() Or ([Course details].CourseDate) Is Null) AND ((StudentInformation.Studentattendingstatus) Is Null))
ORDER BY [Courses Title].WebWordApp, [Courses Title].WebWordLevel, [Course details].CourseDate
WITH OWNERACCESS OPTION;
 
 
**********LATEST SQL SERVER VIEW ATTEMPT**************
SELECT     TOP (100) PERCENT dbo.[Course details].ShowBookingOnWeb, dbo.[Course details].CourseID, dbo.[Courses Title].WebWordApp, dbo.[Courses Title].WebWordLevel, 
                      dbo.[Courses Title].CourseTitleID, dbo.[Courses Title].CourseTitle, dbo.[Courses Title].CourseType, dbo.[Courses Title].CourseDescription, 
                      dbo.[Courses Title].CourseRequirements, dbo.[Course details].CourseDate, dbo.[Course details].CourseTime, CASE WHEN COUNT([studentIDcard]) 
                      < 15 THEN CAST('<a href="http : / / ittraining.lse.ac.uk / students / booking.asp ? courseID = ' AS varchar) 
                      + [Course Details].courseid + CAST('")> Reserve a place < / a > ' AS varchar) ELSE CAST(' DROP - IN places ONLY ' AS varchar) END AS Placesavailable, 
                      dbo.StudentInformation.Studentattendingstatus
FROM         dbo.[Courses Title] INNER JOIN
                      dbo.[Course details] INNER JOIN
                      dbo.StudentInformation ON [Course details].CourseID = StudentInformation.CourseID ON [Courses Title].CourseTitleID = [Course details].CourseTitleID
GROUP BY dbo.[Course details].ShowBookingOnWeb, dbo.[Course details].CourseID, dbo.[Courses Title].WebWordApp, dbo.[Courses Title].WebWordLevel, 
                      dbo.[Courses Title].CourseTitleID, dbo.[Courses Title].CourseTitle, dbo.[Courses Title].CourseType, dbo.[Courses Title].CourseDescription, 
                      dbo.[Courses Title].CourseRequirements, dbo.[Course details].CourseDate, dbo.[Course details].CourseTime, dbo.StudentInformation.Studentattendingstatus
HAVING      (dbo.[Course details].ShowBookingOnWeb = 0) AND (dbo.[Course details].CourseDate >= { fn NOW() } OR
                      dbo.[Course details].CourseDate IS NULL) AND (dbo.StudentInformation.Studentattendingstatus IS NULL)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros