Using ClassQueries() as Tables

Article

Robert Cemper · Mar 14, 2021 1m read

#JDBC #ObjectScript #ODBC #SQL #Caché #Ensemble #InterSystems IRIS

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

0 373

Class dc.test Extends %Persistent { Property p As %Integer; Query LinkedFundsByPerson(p As %Integer) As %SQLQuery(ROWSPEC = "p:%Integer") [ SqlName = CUSTOM_MyQuery, SqlProc ] { SELECT p FROM dc.test where ID IN ( SELECT ID FROM dc.test WHERE p=:p ) } ClassMethod test() { &sql(SELECT * FROM dc.test WHERE EXISTS (SELECT * FROM dc.CUSTOM_MyQuery(ID))) } }

/// Linked Funds via Person->Fund link ClassMethod LinkedFundsByPerson(personId As %Integer) As %Boolean [ SqlName = CUSTOM_MyQuery, SqlProc ] { &sql(SELECT NULL 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 ) ) QUIT ''%ROWCOUNT }

SELECT * FROM TableA WHERE CUSTOM_MyQuery(ID)>0

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

@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))

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)

Robert Cemper · Apr 12, 2021

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

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
   )

)

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?

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

Vitaliy Serdtsev · Apr 12, 2021

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:

O dear! Sorry

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

For example:

/// Linked Funds via Person->Fund link
ClassMethod LinkedFundsByPerson(personId As %Integer) As %Boolean [ SqlName = CUSTOM_MyQuery, SqlProc ]
{
  &sql(SELECT NULL 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
    )
  )
  QUIT ''%ROWCOUNT
}
SELECT *
FROM TableA
WHERE CUSTOM_MyQuery(ID)>0

2 0

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

Using ClassQueries() as Tables

Social networks

InterSystems resources