Creating a view that uses customized class query stored procedure results in compile error

Primary tabs

SQL, Caché

I have the following class: 

 

Class P.RA.SII.F
{


Query ClosedFutures(FromDate As %Date, ToDate As %Date) As %Query(ROWSPEC = "FundCode:%String, Asset:%String, TradeDate:%Date,BuySell:%String, Price:Data.Common.Numeric, Quantity:%Numeric, CumulativePosition:%Numeric, PreviousCumulativePosition:%Numeric, ClosingTrades:%Integer, ClosingTradesInPeriod:%Integer, ClosedTradeValueInPeriod:Data.Common.Numeric, NetQuantityInPeriod:%Integer, WeightedClosedAverageTradePriceInPeriod:Data.Common.Numeric, InitialTradeDate:%Date, InitialTrade:%Integer, LastInPeriod:%Integer") [ SqlProc ]
{
}

 

ClassMethod ClosedFuturesExecute(ByRef qHandle As %Binary, FromDate As %Date, ToDate As %Date) As %Status
{
// Contains complex implementation including setting ^||justClosedTrades
}

 

ClassMethod ClosedFuturesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
kill ^||justClosedTrades
Quit $$$OK
}

 

ClassMethod ClosedFuturesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
set nextid=$order(^||justClosedTrades(qHandle),1,rec)
if $length(nextidset Row=$listbuild($piece(rec,"~",1),$piece(rec,"~",2),$piece(rec,"~",3),$piece(rec,"~",4),$piece(rec,"~",5),$piece(rec,"~",6),$piece(rec,"~",7),$piece(rec,"~",8),$piece(rec,"~",9),$piece(rec,"~",10),$piece(rec,"~",11),$piece(rec,"~",12),$piece(rec,"~",13),$piece(rec,"~",14), $piece(rec,"~",15), $piece(rec,"~",16)),qHandle=nextid }
else set AtEnd=1,Row="" }

Quit $$$OK
}

}

 

I get following compile error when I try and compile view below.

ERROR #5002: Cache error: <UNDEFINED>AddView+69^%ocsSQLRTDict^oddEXTR("P.RA.SII.F","b","FundCode",91):> ERROR #5030: An error occured when compiling class 'P.RA.SII.V.F.ClosedFutures'

 

This view calls ClosedFutures stored procedure created in class above:

 

Class P.RA.SII.V.F.ClosedFutures [ ClassType = view, ViewQuery = {
SELECT * FROM P_RA_SII.F_ClosedFutures(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }

 

It seems Views are unable to handle %Query, namely ROWSPEC (which is what error above complains about as far as I can see) since they aren't actual SQL stored procedures being a customized class query. 

 

Any ideas on how I can get my view to work?

  • 0
  • 0
  • 61
  • 2
  • 1

Answers

Hello

 

I was able to get this to work.  I think the only chagne I made was to add the SQLName to the query

 

Here is my class with your code

 

Class DC.ProcClass [ Abstract ]
{ Query ClosedFutures(FromDate As %Date, ToDate As %Date) As %Query(ROWSPEC = "FundCode:%String, Asset:%String, TradeDate:%Date,BuySell:%String, Price:%Numeric, Quantity:%Numeric, CumulativePosition:%Numeric, PreviousCumulativePosition:%Numeric, ClosingTrades:%Integer, ClosingTradesInPeriod:%Integer, ClosedTradeValueInPeriod:%Numeric, NetQuantityInPeriod:%Integer, WeightedClosedAverageTradePriceInPeriod:%Numeric, InitialTradeDate:%Date, InitialTrade:%Integer, LastInPeriod:%Integer") [ SqlName = ClosedFuturesProc, SqlProc ]
{
} ClassMethod ClosedFuturesExecute(ByRef qHandle As %Binary, FromDate As %Date, ToDate As %Date) As %Status
{
// Contains complex implementation including setting ^||justClosedTrades
} ClassMethod ClosedFuturesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
kill ^||justClosedTrades
Quit $$$OK
} ClassMethod ClosedFuturesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
set nextid=$order(^||justClosedTrades(qHandle),1,rec)
if $length(nextid) set Row=$listbuild($piece(rec,"~",1),$piece(rec,"~",2),$piece(rec,"~",3),$piece(rec,"~",4),$piece(rec,"~",5),$piece(rec,"~",6),$piece(rec,"~",7),$piece(rec,"~",8),$piece(rec,"~",9),$piece(rec,"~",10),$piece(rec,"~",11),$piece(rec,"~",12),$piece(rec,"~",13),$piece(rec,"~",14), $piece(rec,"~",15), $piece(rec,"~",16)),qHandle=nextid }
else set AtEnd=1,Row="" } Quit $$$OK
} }

 

and here is the view using my schema and SQLName

Class DC.NewClass1 [ ClassType = view, ViewQuery = {
SELECT FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }

 

 

 

It seems that views in cache need to start with a "SELECT ..." therefore to get around this, I did the following which works: 

Class DC.NewClass1 ClassType = view, ViewQuery = {
SELECT '', As A, '' As B WHERE 1=2

UNION

SELECT A,B FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }

In views there is an argument called

Not ProcedureBlock

Take out the Not and make it ProcedureBlock

Now you can have it run the same query as you would have in the sql browser with the sql procedure