ExecuteSQL() list

[doughemi]

Trying to wrap my head around ExecuteSQL().  I haven't used it enough to be fluent in it yet.

 

I have a calculation in the Classes table to count the number of boats in a region:

ExecuteSQL(
"SELECT count ("OwnerID")
FROM "Class Member Data"
WHERE
"Region" = ? 
and  "ClassID" = ?
and "ownerCurrent" = ?";
"";""; 1; ID; 1; 1
)

(ID is a field in the Classes table.)  

 

Which gives me a number, but not the same number as performing a Find for Region = 1 and ClassID = Classes::ID and ownerCurrent = 1 in a Class  Member Data layout.

 

So I want to create a list of OwnerIDs to see where the discrepancy lies to allow me to figure out the reason for it.

 

I created another calculation field

ExecuteSQL(
"SELECT"OwnerID"
FROM "Class Member Data"
WHERE
"Region" = ? 
and  "ClassID" = ?
and "ownerCurrent" = ?";
"";""; 1; ID; 1; 1
)

which only returns one OwnerID, where I was expecting a list of all OwnerIDs that fit the WHERE criteria.

 

What have I overlooked or misunderstood?

 

[john renfrew]

Are the region, ClassID and ownerCurrent fields numbers or text?

Why 4 optional variables when you are using 3?

What happens if you hard code the query into the select first?