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:


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.ExternalReference
FROM QUASAR_KYC_Person.Person Person

    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


Hi, Thanks! I get the same error when doing a WHERE 0< ...

CUSTOM_MyQuery is a SELECT statement with a parameter. It seems to work fine if I pass in a value, but now a column reference, but I'm not sure why? What's strange is if I replace the call to CUSTOM_MyQuery with the whole contents of that SELECT statement, with TableA.ID instead of the parameter, it works.

My real-world CUSTOM_MyQuery is 

/// Linked Funds via Person->Fund link
Query LinkedFundsByPerson(personId As %Library.Integer, RWOOnly As %Library.Boolean) As %Library.SQLQuery(CONTAINID = 0, ROWSPEC = "Number:%Integer, Name:%String, Status:%String, LinkType:%String")
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
WHERE Person->InternalReference=:personId

(the second parameter isn't used but is there for legacy reasons - doesn't seem to matter if I remove it)

Oh I see! But doesn't that leave the same issue I have with disabling the UNION'd second half at run time? The only way I can think of is passing it in as a parameter and have the second WHERE clause including

WHERE :doUnion = 1

...but that will still do the second query, and discard it after the fact, I believe, which is inefficient. It might be possible to do with via a CASE statement or IF ELSE somehow? Not sure...

Yes I can put a UNION within a single Class_Query - that's my current approach (see first post), but the issue I have is disabling the UNION half, either within the SQL statement (inelegant and it appears inefficient, at least the way I tried in my first post), or somehow running two Class_Queries into one ResultSet (with the second being enabled at runtime based on a parameter).

The queries are too complex to maintain as strings and concatenate them together that way. They need to remain Class_Queries.

Null arg is not the same as an empty string arg (""), as usual in COS.

yes, but the docs for $ZF(-100) say

You can specify a null argument as ""


so I would expect the function to pass this to the current shell as a viable null argument (or maybe not at all)

Why did you undertake this task, changing of $zf(-1) to $zf(-100), at all? Do you clearly understand the kind of treat you try to eliminate?

we have been specifically instructed by intersystems to do this. $ZF(-1) is deprecated also. I can think of a few ways you could exploit the existing function but I think it's probably not good for me to say them in a public forum :) it's used so infrequently in our code (and mostly legacy stuff) that's easier to just replace them all than work out if we have any specific vulnerabilities.

Hi Again!

I'm not sure if this related, but I am seeing strange behaviour when I try and calculate and array property.


 Property Amount2 As array Of %Numeric [ SqlComputeCode = { set {*} = ##class(ContainerSize).GetTotalAmount2({%%ID})}, SqlComputed, SqlComputeOnChange = (%%UPDATE, %%INSERT), Transient ];

Even if my method does nothing but quits out with a new Array object:

 quit ##class(%Collection.ArrayOfDT).%New()

When I open an instance:

macdocs:MACDEV1>w ##class(MacDocs.SalesMarketing.Quotation.ContainerSize).%OpenId("0||WALK04332||1||20").Amount2
 Set ptr=0 While $listnext(serialcol,ptr,item) { Set i%Amount2($list(item))=$lis
t(item,2) }
macdocs:MACDEV1 2e1>w ##class(MacDocs.SalesMarketing.Quotation.ContainerSize).%OpenId("0||WALK04332||1||20").Amount2

Bizarrely it works the second time?!

Perhaps there's a better way of calculating array properties upon object load?