Published on InterSystems Developer Community (https://community.intersystems.com)

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

Question
Abbad Minhas · Nov 11, 2019

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

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

}

 

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?

#Error Handling #SQL #Caché

Source URL:https://community.intersystems.com/post/creating-view-uses-customized-class-query-stored-procedure-results-compile-error