Article
Robert Cemper · Mar 14, 2021 1m read

Using ClassQueries() as Tables

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

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

#JDBC #ObjectScript #ODBC #SQL #Caché #Ensemble #InterSystems IRIS
110
1 0 11 278
Log in or sign up to continue
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

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

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

00
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

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

)

00
Robert Cemper · Apr 12, 2021

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?

00
Chris Bransden · Apr 12, 2021

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

00
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:

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

}
00
Vitaliy Serdtsev · Apr 12, 2021

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
20