Using ClassQueries() as Tables
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).
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
@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))
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
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)
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
You can't pass IDENTIFIER to class query, but only LITERALS. This is akin to error 5262:
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:
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...
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue