AnsweredAssumed Answered

Using a multi-value field in ExecuteSQL() query

Question asked by davehob on Jul 30, 2014
Latest reply on Jul 31, 2014 by erolst

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.

 

Dave.

Outcomes