Written by

~~ retired but not tired ~~

MOD

Article Robert Cemper · Mar 14, 2021 1m read

Using ClassQueries() as Tables

#Other

Similar to Caché so also in IRIS, you have ClassQueries available in 2 variants

Basic class queries, which use the class %SQLQuery and an SQL SELECT statement.
Custom class queries, which use the class %Query and custom logic to execute, fetch, and close the query.

The related documentation is excellent and very detailed.
You will see a bunch of examples of how to use it with %ResultSet or %SQL.Statement
Following the suggestions, you will add the keyword [SqlProc] to make it visible in SMP or over JDBC/ODBC

Attention: [SqlProc] Doesn't distinguish whether your procedure returns a scalar value (acting as StoredFunction)
or if it returns a ResultSet. You have to check the definition to see the difference.

Returning a ResultSet means that you can use this StoredProcedure not only by a CALL statement
but also like any Sql Table applying WHERE, GROUP, ORDER, JOIN, .. whatever is available to a SELECT

This fact is not new but never mentioned in any documentation I know of.
Examples:

SELECT TOP 5 * FROM SYS.Process_JOBEXAM() ORDER BY Commands DESC

SELECT Name,Directory,MountRequired FROM Config.Databases_List() order by Name

My suggestion:

Understand a Stored Procedure returning a ResultSet as kind of a Sub-Select (with additional parameters).

Discussion (11)0

Add reply

Comments

Chris Bransden · Apr 11, 2021

If I use a class query in an EXISTS clause like:

SELECT *
FROM TableA
WHERE EXISTS (SELECT * FROM CUSTOM_MyQuery(TableA.ID))

I get an error of the type:

LITERAL ('TABLEA') expected, IDENTIFIER (TABLE) found DECLARE QRS CURSOR FOR ...

Is there a way around this?

Thanks,
Chris

0 0

Robert Cemper Apr 11, 2021 to Chris Bransden

@Chris Bransden
Without knowing the definition of CUSTOM_MyQuery(par) it's no possible to answer.
The error message indicates that a literal is expected but indeed TableA.ID is a column reference and you feed a whole resultset instead of a single value

My interpretation: You want to see the rows found by CUSTOM_MyQuery()
which is indeed a classical inner join.

So what is the result returned by SELECT * FROM CUSTOM_MyQuery(??) ?

You may try this transformation that does the same in principle

SELECT * FROM TableA
WHERE 0<(SELECT count(*) FROM CUSTOM_MyQuery(TableA.ID))

0 0

Chris Bransden Apr 12, 2021 to Robert Cemper

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)

0 0

Robert Cemper Apr 12, 2021 to Chris Bransden

indeed by CUSTOM_MyQuery(TableA.ID) you just pass 1 parameter

but LinkedFundsByPerson(TableA.ID,1) might work
as the signature expects 2 params some related code is generated
even if the second param isn't used at all.
You can test the Stored Procedure interactive from SMP>SQL>storedProcedures
and you should be asked for 2 params

0 0

Chris Bransden Apr 12, 2021 to Robert Cemper

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
   )

)

0 0

Robert Cemper Apr 12, 2021 to Chris Bransden

this looks strange to me LITERAL ('PERSON') expected, IDENTIFIER (Person) found

with not much hope:
does CAST(TableA.ID AS INTEGER) instead of TableA.ID make a difference?

0 0

Chris Bransden Apr 12, 2021 to Robert Cemper

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

0 0

Vitaliy Serdtsev Apr 12, 2021 to Chris Bransden

You can't pass IDENTIFIER to class query, but only LITERALS. This is akin to error 5262:

Cannot project query with parameters '%1' as view

Here is a simple demo of this issue:

<FONT COLOR="#000080">Class dc.test Extends %Persistent
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">p </FONT><FONT COLOR="#000080">As %Integer</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Query </FONT><FONT COLOR="#000000">LinkedFundsByPerson(</FONT><FONT COLOR="#ff00ff">p </FONT><FONT COLOR="#000080">As %Integer</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %SQLQuery</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">ROWSPEC </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"p:%Integer"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">CUSTOM_MyQuery</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlProc </FONT><FONT COLOR="#000000">]
{
</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#008000">p </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test
</FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000000">IN
(
</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#008000">ID
</FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test
</FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">p</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">:p
</FONT><FONT COLOR="#000000">)
}
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">test()
{
  </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080"> FROM </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#000000">EXISTS (</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080"> FROM </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">CUSTOM_MyQuery</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="red">ID))</FONT><FONT COLOR="#800080">)
</FONT><FONT COLOR="#000000">}
}</FONT>

0 0

Robert Cemper Apr 12, 2021 to Vitaliy Serdtsev

O dear! Sorry

0 0

Vitaliy Serdtsev Apr 12, 2021 to Chris Bransden

You can still avoid code duplication. To do this, you need to make a function, and already use it in query.

For example:

<FONT COLOR="#000080">/// Linked Funds via Person->Fund link
ClassMethod </FONT><FONT COLOR="#000000">LinkedFundsByPerson(</FONT><FONT COLOR="#ff00ff">personId </FONT><FONT COLOR="#000080">As %Integer</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Boolean </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">SqlName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">CUSTOM_MyQuery</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlProc </FONT><FONT COLOR="#000000">]
{
  </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080">NULL FROM </FONT><FONT COLOR="#008000">QUASAR_Core_Client</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Client </FONT><FONT COLOR="#000080">AS </FONT><FONT COLOR="#008000">Client
      </FONT><FONT COLOR="#000080">INNER JOIN </FONT><FONT COLOR="#008000">QUASAR_KYC_Fund</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Fund </FONT><FONT COLOR="#000080">AS </FONT><FONT COLOR="#008000">Fund </FONT><FONT COLOR="#000080">ON </FONT><FONT COLOR="#008000">Client</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Number</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">Fund</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">InternalReference
      </FONT><FONT COLOR="#000080">LEFT JOIN </FONT><FONT COLOR="#008000">QUASAR_GDPR_Close_Fund</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">FundClosure </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#008000">FundClosure </FONT><FONT COLOR="#000080">ON </FONT><FONT COLOR="#008000">Client</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Number</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">FundClosure</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">ID
    </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">Client</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">ClientMarketIndicator</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'C'
    </FONT><FONT COLOR="#000000">AND </FONT><FONT COLOR="#008000">Client</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Number </FONT><FONT COLOR="#000000">IN
    (
    </FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#008000">Client
    </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">QUASAR_KYC_Person</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">FundLink
    </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">Person</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">InternalReference</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">:personId
    </FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">QUIT </FONT><FONT COLOR="#000000">''</FONT><FONT COLOR="#800000">%ROWCOUNT
</FONT><FONT COLOR="#000000">}</FONT>
SELECT *
FROM TableA
WHERE CUSTOM_MyQuery(ID)>0

0 0

Chris Bransden Apr 12, 2021 to Vitaliy Serdtsev

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#co…

0 0