· Oct 10, 2017

Create Custom Table or View

Hi ,

I am newbie working on Cache .

I have a global ^BB("QA",QDJ,QTM+i) , i want to create a table or a view to access the data from this.

I am expert in creating tables & views on RDBS like Oracle, SQL server. But no idea on Cache.

Can someone guide me from scratch how to pull the data from the global.

I have installed Cache Studio on my machine.


Discussion (3)1
Log in or sign up to continue

here an example of my request , it can help you:


method CheckMissedMeasure() As %Status

 //selectionner tous les patients en retard  
 set myquery=3
 set myquery(1) = "SELECT profile_id,Type_Measure, Program as Heure_Ratée,Practitioner_Id,Warning_Type FROM Bwxpert_Repository.Enrollment" // enrollment
 set myquery(2) = " where GETDATE()>NEXT_MEASURE"
 set myquery(3)="and Alert=0"
  set tStatement = ##class(%SQL.Statement).%New()
 set qStatus = tStatement.%Prepare(.myquery)
  if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  set rset = tStatement.%Execute()
     set array = []
     set json ={}
     while rset.%Next() {
       set json ={}
       set json.Profile= rset."profile_id"
       set json.MissedMeasure= rset."Type_Measure"
       set json.MissedPeriod= rset."Heure_Ratée"
       set json.Practionner= rset."Practitioner_Id"
       set json.ContactType= rset."Warning_Type"
       write json.%ToJSON()
       do array.%Push(json)

Hi Praveen,

this is not going to be an exhausting answer but rather a summary of choices you have.

First thing to answer: are you working with a legacy application, that stored data in globals, not using our Cache persistent classes? In this case, you would like to follow Sean's globals to classes mapping guide.

Another option, suitable in cases where you have a mix of persistent classes and only some data stored directly in globals, you may consider using custom SQL queries. In such query you implement code that iterates over your global nodes and expose result as SQL resultset. You can then call query as a standard stored procedure. see Cache online reference for mode details here.

There is one more option too, which could be used in some special cases when global is a simple structure. In such case you could create a new persistent class definition and simply override the storage generated during its compilation to reflect your global structure. This is less flexible than using SQL mapping mentioned as first choice, but could be easier for you.