New post
AllAccepted answers
Chris Bransden · Apr 12, 2021

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

)

Chris Bransden · Apr 12, 2021

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
(
SELECT Client
FROM QUASAR_KYC_Person.FundLink
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)

Chris Bransden · Mar 12, 2021

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...

Chris Bransden · Mar 12, 2021

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.

Chris Bransden · Jul 5, 2018

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 ""

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

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.

Chris Bransden · Jul 13, 2017

Hi Again!

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

Eg:

 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) }
<LIST>zAmount2GetSwizzled+5^MacDocs.SalesMarketing.Quotation.ContainerSize.1
macdocs:MACDEV1 2e1>w ##class(MacDocs.SalesMarketing.Quotation.ContainerSize).%OpenId("0||WALK04332||1||20").Amount2
3@%Collection.ArrayOfDT

Bizarrely it works the second time?!

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