Link to home
Start Free TrialLog in
Avatar of keithwilson1
keithwilson1Flag for United States of America

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(COURSE)-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)
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Qlemo
Or replace
(StudentGrade = @Grade OR LEN(ISNULL(@Grade,'')) = 0)

Open in new window

with
StudentGrade = isnull(@Grade, StudentGrade)

Open in new window

at least if you can be sure @Grade is never '' but rather NULL if it should get ignored.