title: Using CASE with multiple conditions in Subquery
body: <p>1) Want all records associated with primary key OR_LOG.LOG_ID where OR_LOG.LOC_ID = 4060020</p>
  
  <p>2) Want a column to output "yes" or "no" if the listed Medication_ID was given between the times listed in the script.  Really I want to include 4 medications and have the "yes" or "no" if any of them were given.</p>
  
  <p>3) I was able to get this to sort of work before I added the two time restrictions except it seemed to only show rows that included the medication being given.  I thought the left join was going to take care of that.  When I added the two "and" time statements I got an "invalid character" error.</p>
  
  <p>4) I will have a few more of the same types of subqueries to run using different time frames and different medication_IDs.</p>
  
  <p>5) The database is very large and I am trying be as efficient as I can.</p>
  
  <pre><code>SELECT 
     CLARITY.OR_LOG.LOC_ID,
     CLARITY.V_LOG_BASED.PRIMARY_PREOP_NURSE_NM,
     CLARITY.V_LOG_BASED.PRIMARY_PROCEDURE_NM,
     CLARITY.V_LOG_BASED.PRIMARY_PHYSICIAN_NM,
     CLARITY.F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID,
     CLARITY.V_LOG_BASED.PRIMARY_RECOVERY_NURSE_NM,
     CLARITY.V_LOG_BASED.IN_OR_DTTM,
     CLARITY.V_LOG_BASED.OUT_OR_DTTM,
      Case
         When CLARITY.ORDER_MED.MEDICATION_ID = 104661
         and CLARITY.MAR_ADMIN_INFO.TAKEN_TIME  &gt; CLARITY.V_LOG_TIMING_EVENTS.PATIENT_IN_PREPROCEDURE_DTTM
         and CLARITY.MAR_ADMIN_INFO.TAKEN_TIME &lt; CLARITY.V_LOG_TIMING_EVENTS.PATIENT_OUT_ROOM_DTTM
         then 'YES'
         else 'NO' END AS PONV
  FROM CLARITY.OR_LOG
  INNER JOIN CLARITY.F_AN_RECORD_SUMMARY
  ON CLARITY.OR_LOG.LOG_ID = CLARITY.F_AN_RECORD_SUMMARY.LOG_ID
  INNER JOIN CLARITY.V_LOG_BASED
  ON CLARITY.OR_LOG.LOG_ID = CLARITY.V_LOG_BASED.LOG_ID
  left JOIN CLARITY.MAR_ADMIN_INFO
  ON CLARITY.F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID =     CLARITY.MAR_ADMIN_INFO.MAR_ENC_CSN
  inner JOIN CLARITY.ORDER_MED
  ON CLARITY.MAR_ADMIN_INFO.ORDER_MED_ID = CLARITY.ORDER_MED.ORDER_MED_ID
  INNER JOIN CLARITY.V_LOG_TIMING_EVENTS
  ON CLARITY.F_AN_RECORD_SUMMARY.LOG_ID = CLARITY.V_LOG_TIMING_EVENTS.LOG_ID
  WHERE CLARITY.OR_LOG.LOC_ID           = 4060020
  </code></pre>