Selecting IF?

acidburn85 · #1 (**permalink**) 01-05-12, 15:31

Ok so I have this script. I need it to behave like this, if the S.Term='3' then grab the data from S.WDIS otherwise leave it alone and pull whatever is in the term column. Basically that data in s.term can't be changed but I need to be able to have multiple values if it is a 3 (which denotes it as a year long class) so I have another column with the correct term code in it but the other values are all correct which are 1 and 2.

Code:

SELECT DISTINCT
	S.Course_ID, T.HR_Code, S.Section_Number, S.Room_Number, S.Period_Start, S.Term
   FROM Master_Schedule S
   LEFT JOIN Teacher T
   ON S.School_Year = T.School_Year AND S.Location = T.Teacher_School_Num AND
	S.Teacher_SSN = T.Teacher_Local_ID
		
   WHERE S.School_Year=1112 AND
	S.Location=0021 AND
	Teacher_Room<>'SFCC' ---Removes College Teachers
	ORDER BY S.Course_ID, S.Section_Number

Pat Phelan · #2 (**permalink**) 01-05-12, 16:26

Code:

SELECT DISTINCT
   S.Course_ID, T.HR_Code, S.Section_Number
,  S.Room_Number, S.Period_Start, S.Term
--  vvvvv  ptp  20120105  Demo how to pick between multiple expressions for a column value
,  CASE
      WHEN '3' = S.Term THEN S.WDIS
      ELSE S.Term
   END AS CaseDemo
--  ^^^^^  ptp  20120105  Demo how to pick between multiple expressions for a column value
   FROM Master_Schedule S
      LEFT JOIN Teacher T
         ON S.School_Year = T.School_Year 
         AND S.Location = T.Teacher_School_Num 
         AND S.Teacher_SSN = T.Teacher_Local_ID
   WHERE S.School_Year = 1112
      AND S.Location=0021
      AND Teacher_Room<>'SFCC' ---Removes College Teachers
   ORDER BY S.Course_ID, S.Section_Number

-PatP

acidburn85 · #3 (**permalink**) 01-05-12, 19:50

Thanks! That looks great! I owe you one. I had tried something similar buy my syntax was wrong! Thanks again!

acidburn85 · #4 (**permalink**) 01-06-12, 07:38

Ok I just tried it, but I was hoping to have it put data into the blanks rather than make a new column. Because the system this is importing only looks in one column. Is there any way to substitute the three with whatever is in S.WDIS?

acidburn85 · #5 (**permalink**) 01-06-12, 08:07

Never mind I figured it out!

Thanks for your help!!

Code:

SELECT DISTINCT
  S.Course_ID, T.HR_Code, S.Section_Number
,  S.Room_Number, S.Period_Start
--  vvvvv  ptp  20120105  Demo how to pick between multiple expressions for a column value
,  CASE
     WHEN '3' = S.Term THEN S.WDIS_Schl_Use
     WHEN '1' = S.Term THEN S.Term
     WHEN '2' = S.Term THEN S.Term
     ELSE S.Term
  END AS Term
--  ^^^^^  ptp  20120105  Demo how to pick between multiple expressions for a column value
  FROM Master_Schedule S
     LEFT JOIN Teacher T
        ON S.School_Year = T.School_Year
        AND S.Location = T.Teacher_School_Num
        AND S.Teacher_SSN = T.Teacher_Local_ID
  WHERE S.School_Year = 1112
     AND S.Location=0021
     AND Teacher_Room<>'SFCC' ---Removes College Teachers
  ORDER BY S.Course_ID, S.Section_Number, Term

Internet Services

Web Development

Digital Marketing

Consumer Tech