wsvp

ExecuteSQL Multi-value comparison

Discussion created by wsvp on Oct 1, 2014
Latest reply on Sep 24, 2015 by beverly

After spending an ENORMOUS amount of time trying to solve/research this issue, I am hoping someone here can either help or point me in the right direction.

 

What I am basically looking to do, is establish a match in ExecuteSQL "EXACTLY" like we do with FileMaker's "=" Relationship operator, where ANY "SINGLE" value in Table A ( MAIN::zz_c_Format_SQL ) = ANY "SINGLE" value in Table B ( ACNT::zz_c_Format_SQL ) a result is returned, regardless of how many other values or the order of the values exist. Also it is an exact "value" match, but "not" a pattern match.

 

While I am a beginner with SQL, I do understand that SQL does not work with carraige return delimited list values, as we do in FM, so I have created calculations to convert the lists to Comma delimited values, with each text value enclosed in single quotes. (thats what the fields named "zz_c_Format_SQL" do )

 

Example...

 

MAIN::zz_c_Format_SQL

 

Value examples (any combination in any order could be selected):

'Contact','Client','Vendor','Bank','Location','Active Vendor' ... etc

 

ACNT::zz_c_Format_SQL

 

Value examples (any combination in any order could be selected):

'Contact','Client','Vendor','Bank','Location','Active Vendor' ... etc

 

In a FileMaker Relationship using the "=" operator, "ANY" "value" match between the 2 Table/Field's will return a result.

 

in ExecuteSQL I have not been able to achieve the same result.

 

I have tried HUNDREDS of permutations using WHERE with LIKE, IN, =, using wildcards, also tried all different kinds of JOIN approaches. I have gone through hundreds of threads and references, and the most common thing that I have observed, are people recommending to NOT use fields with multiple values. I really hope this is not true, as it would make ExecuteSQL far less useful to me than I previously assumed it would be.

 

ExecuteSQL ( "

SELECT zz_c_Account_Name

FROM ACNT

WHERE

ACNT.zz_c_Format_SQL IN ( ? )

ORDER BY zz_c_Account_Name

" ; "" ; "" ; MAIN::zz_c_Format_SQL )

 

The above formula is one of hundreds that I have tried... it will only match if the ACNT::zz_c_Format_SQL field has exactly the same data as the MAIN::zz_c_Format_SQL. Even the order of the values has to be the same to get a result.

 

Any help would be greatly appreciated.

Outcomes