0 Replies Latest reply on Sep 4, 2014 9:33 AM by ThomasM_1

    Comparing two lists using ExecuteSQL

    ThomasM_1

      Title

      Comparing two lists using ExecuteSQL

      Post

      Is it possible to compare two lists in an SQL query? I have a rather complex query using joins and multiple criteria. Everything is working fine except the statement where the field queried (Norm::Sex) can contain more than one value (male and/or female). I’d like to get results if there is an intersection. The relevant part of the query looks like this:  

      SELECT n.Name
      FROM Norm AS n
      WHERE n.Sex = ? 

      doesn’t work even if all entries in Norm::Sex are the same.

       

      WHERE n.Sex LIKE " & nSex

      doesn’t work with 

      nSex = "'" & Norm::Sex & "'";

      nor with 

      nSex = "%" & GetValue ( Norm::Sex ; 1 ) & "%" ;

       

      WHERE n.Sex IN " & nSex

      doesn’t work with 

      nSex = "(" & Substitute ( List ( Norm::Sex ) ; ¶ ; "," ) & ")" ;

      nor with 

      nSex = "(" & Substitute ( List ( Norm::Sex ) ; ¶ ; "," ) & "," & Norm::Sex & ")" ;

       

      Any ideas?