Could anybody help with the syntax of this query (assuming it’s possible). I need to refine this query to find values where a key value is present in a multi-value field, id_RSCBlock. (This can hold any number of return-separated values - the values actually indicate Resources which are blocked (i.e. can't be booked) when id_RSC is booked).
Let ( [~query = "SELECT DISTINCT r.id
FROM SSN s, PGM p, RBK rb, RSC r
WHERE s.id_PGM = p.id
AND rb.id_SSN = s.id
AND rb.id_RSC = r.id
(and where rb.id_RSC is present IN r.id_RSCBlock)
AND s.timestampFrom < ?
AND s.timestampTo > ?"];
ExecuteSQL( ~query ; "" ; "" ;GUI::TIMESTAMPTO; GUI::TIMESTAMPFROM)
I believe I need an IN clause, but attempts so far have failed. I’ve tried this:
AND rb.id_RSC IN (' " & substitute (r.id_RSCBlock;"¶";"','"))
but that’s clearly wrong (can’t use field names in the Substitute), and as far as I understand I can’t use a calculation variable to reference id_RSCBlock.
I’m still getting to grips with ExecuteSQL(), so any suggestions would be really appreciated.