AnsweredAssumed Answered

Comparing two lists using ExecuteSQL

Question asked by ThomasM_1 on Sep 4, 2014

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?

Outcomes