Results 1 to 5 of 5

Thread: Selecting IF?

  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Selecting IF?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    22
    Thanks! That looks great! I owe you one. I had tried something similar buy my syntax was wrong! Thanks again!

  4. #4
    Join Date
    Sep 2011
    Posts
    22
    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?

  5. #5
    Join Date
    Sep 2011
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •