Thanks, although I don't think that works for me as one of the other contexts I need to access this LinkedFundsByPerson query are as a select query to to return those 4 columns in the original into a result set, and another context is I use it within a UNION query as suggested here: https://community.intersystems.com/post/possible-merge-two-resultset#com...
Then I get error
LITERAL ('CAST') expected, CAST found
It's like it's unable to process anything other than literal values as parameters for class queries called within SQL. A shame as I would like to avoid duplicating such queries
I tried with the parameter (or removing it from the query entirely), but no change. Indeed, the error message only refers to the first parameter:
LITERAL ('PERSON') expected, IDENTIFIER (Person) found DECLARE QRS CURSOR FOR SELECT ...
If I send a literal value ('123') then everything works, but it doesn't seem to want to deal with a column reference, but I don't understand why if it can cope with that fine when I expand out the query? eg
SELECT Person.ExternalReferenceFROM QUASAR_KYC_Person.Person PersonWHERE NOT EXISTS (
SELECT Fund.InternalReference,Fund.ShortName,ISNULL(FundClosure.ClosureStatus,'Open') AS ClosureStatus,'P' AS Linktype FROM QUASAR_Core_Client.Client AS Client INNER JOIN QUASAR_KYC_Fund.Fund AS Fund ON Client.Number=Fund.InternalReference LEFT JOIN QUASAR_GDPR_Close_Fund.FundClosure As FundClosure ON Client.Number=FundClosure.ID WHERE Client.ClientMarketIndicator='C' AND Client.Number IN ( SELECT Client FROM QUASAR_KYC_Person.FundLink WHERE Person->InternalReference=Person.ID )
Log in or create a new account to continue