New post
Question
· Nov 11, 2019

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

#Error Handling #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(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?

Discussion (3)0
Log in or sign up to continue
Brendan Bannon · Nov 13, 2019

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

0 0
Abbad Minhas · Nov 13, 2019

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

0 0