richard.noordam (6/2/2010)
I'm having trouble with the following bit. I have a fairly complex set of case statments but the following is what I'm having trouble with (most inside portion)
ELSECASE
WHEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID is NULL THEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join AS vw_BASE_WebData_FacultyWebs_Classes_Join_2 WHERE InstructorName = dbo.vw_BASE_CCSSM_REP_Class_D.INSTR_NAME AND WDFC_CI_DeptDiv = dbo.vw_BASE_CCSSM_REP_Class_D.CI_DEPT_DIV AND WDFC_CI_Course_Num = dbo.vw_BASE_CCSSM_REP_Class_D.CI_COURSE_NUM AND WDFC_Class_YRQ = dbo.vw_BASE_CCSSM_REP_Class_D.CLASS_YRQ
ELSE SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID
END
what's the hang up or am I trying something that shouldn't/can't be done this way? All I'm trying to do is see which condition exists in this extra table, and fill the data based on that, essentially returning a URL that goes to a specific class for a student.
It's a little hard to tell without seeing the rest of the query or having any test data or table definitions ...
But, it seems to me this would be much easier to support and troubleshoot and would have superior performance if instead of doing multiple selects within a CASE statement you handle it in a more set-based fashion. Establish the correct joins in your query and then do a CASE based on the values of the records in the joined tables.