General ExecuteSQL() questions...

[Matt Klein]

Hi all -

 

I am just now beginning to play around with the new ExecuteSQL() function in FM12 and I have a couple of questions regarding it's use:

 

1) Is it possible to use ExecuteSQL to select a set of records where a field on those records as a certain value in it when that field contains more than one value on each record?  Kind of like using a multi-key field in a relationship.

 

2) Along those same lines....if you create a join in a SQL query do the fields used in the join have to contain a single value each or can either or both fields have multiple values?

 

 

Thanks!

[Ocean West]

it's generally avoided to use sql to query a "multi-key" you would have to use a %like% operator if memory serves.

 

If you haven't had a chance try out the sample kits for testing:   http://www.seedcode.com/cp-app/ste_cat/sqlxfree or  http://fmforums.com/forum/files/file/25-sql-builder/

[beverly]

Hmm, sorry, just getting back to the forums...

If "multi-line" sort of thing, can you convert it to comma-delimited instead of return-delimited and use WHERE .. IN ... ? That's basically an 'OR' query using a list of values. Numbers are not single quoted value in the list, but text is:

 

WHERE xxx IN (1,2,3)

 

WHERE yyy IN ('a','b','c')

 

With some calcing this should work using your "values".