asked on

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

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)

Open in new window

ASKER CERTIFIED SOLUTION

andy232

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TextReport

The first thing I would do is in Access set the aliases for your tables, this is done in the query design, click on the table and right mouse click then properties, set the alias but you must tab or enter out before selcting the next table.

Once this is done then you can change the HAVING to WHERE as these are all conditions that work on the base data rather than the results of Aggregate functions. In the Access query yolu can do this by putting them as a WHERE on the Group By option.

Finally I would do a query on the query something like the code below.

Cheers, Andrew

SELECT TOP 100 PERCENT
       ShowBookingOnWeb
     , CourseID
     , WebWordApp
     , WebWordLevel
     , CourseTitleID
     , CourseTitle
     , CourseType
     , IIf([coursedate] Is Null,"Date to be confirmed",[coursedate]) AS [Date]
     , CourseDescription
     , CourseRequirements
     , CourseDate
     , CourseTime
     , IIf(Qty<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 
(
SELECT cd.ShowBookingOnWeb
     , cd.CourseID
     , ct.WebWordApp
     , ct.WebWordLevel
     , ct.CourseTitleID
     , ct.CourseTitle
     , ct.CourseType
     , [coursedate]
     , ct.CourseDescription
     , ct.CourseRequirements
     , cd.CourseDate
     , cd.CourseTime
     , Count([SWtudentIDCard]) AS Qty
     , StudentInformation.Studentattendingstatus
 
FROM [Courses Title] ct 
    INNER JOIN ([Course details] cd 
        INNER JOIN StudentInformation si 
        ON cd.CourseID = si.CourseID)
    ON ct.CourseTitleID = cd.CourseTitleID
 
WHERE cd.ShowBookingOnWeb=0
AND   (cd.CourseDate >= SysDate() OR cd.CourseDate Is Null)
AND   si.Studentattendingstatus Is Null
 
GROUP BY cd.ShowBookingOnWeb
       , cd.CourseID
       , ct.WebWordApp
       , ct.WebWordLevel
       , ct.CourseTitleID
       , ct.CourseTitle
       , ct.CourseType
       , [coursedate]
       , ct.CourseDescription
       , ct.CourseRequirements
       , cd.CourseDate
       , cd.CourseTime
       , si.Studentattendingstatus
) t
 
ORDER BY WebWordApp
       , WebWordLevel
       , CourseDate

Open in new window

LSE_IT_Training

ASKER

Thanks so much!