mikebeargie

ExecuteSQL advanced query limits?

Discussion created by mikebeargie Expert on Oct 24, 2012
Latest reply on Jan 31, 2014 by mikebeargie

I am getting a ? from a statement I thought would work, but apparently does not.

 

I have a "RACE_DATA" table that holds historical results from elections by county. So the data looks something like this:

 

a__kp_idKeya_kf_raceIdKeya_kf_countyIdKeycandidateCount
11505
215010
31515
42505
525010
62515

 

I am looking to get the sum of candidateCount, from the MOST RECENT (based on primary key) countyIdKey record, based on race::a__kp_idKey entered as the variable in the statement. So my query should return 15 on the above table (assuming raceIdKey = 1), from serial numbers 2 and 3.

 

My query (as a calc field in the main RACES table) I've tried is as follows:

ExecuteSQL (

"

SELECT sum(candidateCount)

FROM RACE_DATA

QUALIFY

1 = ROW_NUMBER() OVER (PARTITION BY a_kf_countyIdKey ORDER BY a__kp_idKey DESC);

WHERE a_kf_raceIdKey = ?

"

; ""; "" ; a__kp_idKey)

 

 

I am assuming that the Qualify statement is not valid for filemaker's ExecuteSQL.

 

I may be thinking too far into this, but can someone see an obvious solution to why this isn't rendering? I'm doing trial and error with other methods in the meantime.

Outcomes