keithwilson1
asked on
Correct an issue with a where clause with calculation
The last or statement in the where doesn;t work.
SELECT DISTINCT Building,
BuildingName,
StudentId,
SECTION_KEY,
PEIMS_Sequence,
StudentName,
CASE PEIMS_Sequence
WHEN '0' THEN COURSE
ELSE LEFT(COURSE,DATALENGTH(COU RSE)-1)
END
AS NEWCRSENAME,
COURSE,
CourseName,
CREDIT,
SchoolYear,
StudentGrade,
SEM1Grade,
SEM2Grade,
MailingAddress,
MailingCity,
MailingState,
MailingZip
from #tmpRC
where ((PEIMS_Sequence = '0') and ((ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1 and CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1 and CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((SEM1Grade IS NULL) and (ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1 and CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((SEM1Grade IS NULL) and (ISNUMERIC(SEM2Grade) = 1 and CONVERT(int, SEM2Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1 and CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((ISNUMERIC(SEM1Grade) = 1) and (CAST(SEM1Grade AS INT) <= 70)) and ((ISNUMERIC([SEM2Grade]) = 1) and (CAST(SEM2Grade AS INT) > 0)) and (SEM1Grade + SEM2Grade / 2 <= 69.5)) AND
(StudentGrade = @Grade OR LEN(ISNULL(@Grade,'')) = 0)
SELECT DISTINCT Building,
BuildingName,
StudentId,
SECTION_KEY,
PEIMS_Sequence,
StudentName,
CASE PEIMS_Sequence
WHEN '0' THEN COURSE
ELSE LEFT(COURSE,DATALENGTH(COU
END
AS NEWCRSENAME,
COURSE,
CourseName,
CREDIT,
SchoolYear,
StudentGrade,
SEM1Grade,
SEM2Grade,
MailingAddress,
MailingCity,
MailingState,
MailingZip
from #tmpRC
where ((PEIMS_Sequence = '0') and ((ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1 and CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1 and CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((SEM1Grade IS NULL) and (ISNUMERIC(SEM1Grade) = 1 and CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1 and CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((SEM1Grade IS NULL) and (ISNUMERIC(SEM2Grade) = 1 and CONVERT(int, SEM2Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1 and CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((ISNUMERIC(SEM1Grade) = 1) and (CAST(SEM1Grade AS INT) <= 70)) and ((ISNUMERIC([SEM2Grade]) = 1) and (CAST(SEM2Grade AS INT) > 0)) and (SEM1Grade + SEM2Grade / 2 <= 69.5)) AND
(StudentGrade = @Grade OR LEN(ISNULL(@Grade,'')) = 0)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
withOpen in new window
at least if you can be sure @Grade is never '' but rather NULL if it should get ignored.