title: MS SQL Linked Oracle Server join condition
body: <p>I am trying to create a view using both SQL tables and tables from linked Oracle database &amp; the SQL part as below</p>
  
  <pre><code>SELECT empv.FULL_NAME,
         CAST(C_Date + ' ' + SUBSTRING(C_Time, 1, 2) + ':' + SUBSTRING(C_Time, 3, 2) + ':'
         + SUBSTRING(C_Time, 5, 2) AS DATETIME) AS "PUNCH_TIME",
         tT.C_Name "MACHINE_NAME",
         CASE
             WHEN LEN(L_UID) &lt; 4 THEN RIGHT('0000' + CAST(L_UID AS VARCHAR(4)), 4)
             ELSE CAST(L_UID AS VARCHAR(10))
         END AS "EMPLOYEE_NUMBER",
         CASE L_MODE
             WHEN 1 THEN 0
             WHEN 2 THEN 1
             ELSE L_MODE
         END AS "PUNCH_TYPE",
         CASE L_RESULT
             WHEN 0 THEN 'S'
             ELSE 'E'
         END AS "PUNCH_RESULT"
  FROM tEnter T,
       tTerminal tT,
       [PRODBAK]..APPS.XXFPEMPVIEW empv
  WHERE 1 = 1
      AND tT.L_ID = T.L_TID
      AND L_UID &lt;&gt; -1
      AND empv.EMPLOYEE_NUMBER = T.L_UID
      /* AND CAST(GETDATE() AS DATE) BETWEEN empv.effective_start_date and COALESCE(effective_end_date, CAST(getdate() as date))*/
      AND CAST(C_Date + ' ' + SUBSTRING(C_Time, 1, 2) + ':' + SUBSTRING(C_Time, 3, 2) + ':' + SUBSTRING(C_Time, 5, 2) AS DATETIME) 
          BETWEEN empv.effective_start_date AND COALESCE(effective_end_date, CAST(GETDATE() AS DATE))
      AND CAST(C_Date + ' ' + SUBSTRING(C_Time, 1, 2) + ':' + SUBSTRING(C_Time, 3, 2) + ':' + SUBSTRING(C_Time, 5, 2) AS DATETIME)
          &gt; '01/01/2016 00:00:00 AM'
  GO
  </code></pre>
  
  <p>Now I have some issues, MS SQL tables have few user id values (L_UID) those are not matched with the EMPLOYEE_NUMBER column values available with the linked Oracle table. This causes the script to run for long time and finally given up due to values not found. How do I overcome this situation? I am pretty new to MS SQL and the JOIN syntax's are kind of alien for me at this point of time.</p>