specifically in the ELSE Case I get
Error in list of function arguments: 'SELECT' not recognized.
Error in list of function arguments: ')' is not recognized.
Unable to parse query.
I would normally do as you suggested but in this case it isn't as easy as that, and it's a special situation. Back to the point.
Here is the whole case statement. I'm trying to return a specific URL depending on several conditions.
WHEN '85' THEN 'Campus Meetings; See angelccs.spokane.edu'
WHEN '30' THEN
WHEN 'TD' THEN 'See angelccs.spokane.edu'
ELSE 'See http://angel.spokanefalls.edu/'
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 AS URL
the ELSE CASE in the innermost case is the issue, specifically this statement (below) in a WHEN (X) is null, WHERE X is a select statement.
I went down the set based, and couldn't get it to work the way I wanted, (and I'm not an SQL novice, or intermediate and have been doing this, for right about 15 yrs, starting with Oracle and then MSSQL).
In the end I want 1 URL based on the Following logic
CASE (SBCTC_MISC_1) -- SOURCE 1
WHEN '85' THEN URL = angelccs.spokane.edu
WHEN '30' THEN
WHEN 'TD' THEN URL = angelccs.spokane.edu
ELSE URL = http://angel.spokanefalls.edu
WHEN (SELECT STATEMENT TO DETERMINE first URL condition) is null
Then SELECT SQL URL from source_2
Then Select SQL URL from source_3
the color 'tagged' section is the only section I'm having issue with. Not sure why the color didn't show up. I'm SOOOOO close to the solution on this I can just feel it.
Note: Even after all has hooked up properly most of the classes STILL won't have a URL and that is ok, and correct.