Select not in not working for Crystal reports

Question

I want to select on not found. this is crystal reports syntax. Using my patient table view.

Select Patient_number 
from vwgenpatinfo b 
where b.Patient_number 
not in (    
     SELECT "ClinicalAnal_d1110yes"."Patient Number"
     FROM   "Programmer"."dbo"."ClinicalAnal_d1110yes" "ClinicalAnal_d1110yes")

this is getting a failure: Invalid object name "ClinicalAnal_d1110yes"

But this is in the parens works by itself.

Answer 1

the double quotes here are not necessary. Also, it seems like you specify "ClinicalAnal_d1110yes" twice unecessarily. Try to re-write like this:

Select Patient_number from vwgenpatinfo 
where Patient_number not in
(
    SELECT Patient_Number
    FROM   Programmer.dbo.ClinicalAnal_d1110yes
)