New post
Question
Abbad Minhas · 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?

0
0 176
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
Neerav Adam Verma · Nov 29, 2019

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

0 0