go to post Jill Goldberg · Jun 19, 2017 As Eduard mentioned there are a couple of different approaches, depending on what your needs are. Here are some simple classes to use as examples: PetOwner has a name, state and a list of PetsPets has two properties: type and size Class Demo.PetOwner extends %Persistent { Property Name As %String; Property State As %String; Property Pets As list of Pet; } Class Demo.Pet extends %SerialObject { Property Type As %String; Property Size As %String; } Running a simple query produces results with embedded characters and no easy way to parse the list of Pets: SELECT Name, State, Pets FROM Demo.PetOwner One option is to use a stored function to return a single value when querying a specific PetOwner: /// SQL Function to get pet info /// Return each pet info as a comma delimited string where type and size /// are separated by ^ ClassMethod PetInfo(id As %String = "") As %String [SqlProc] { Try { Set sc = $$$OK Set petInfo = "" // Quit if no id Quit:(id="") // Open the PetOwner #dim owner As Demo.PetOwner Set owner = ##class(Demo.PetOwner).%OpenId(id) #dim pet As Demo.Pet Set petCount = owner.Pets.Count() For i=1:1:petCount { Set pet = owner.Pets.GetAt(i) Set petInfo = petInfo _ pet.Type _ "^" _ pet.Size _ "," } // Remove trailing , Set petInfo = $Extract(petInfo,1,*-1) } Catch(ex) { Set sc = ex.AsStatus() } Return petInfo } If PetInfo method is in the Demo.Test class, the stored function can be used like this to get a comma-delimited string of pet information where each component of the pet information is separated by a "^": SELECT Name, State, Demo.Test_PetInfo(ID) from Demo.PetOwner Another option is to write a custom class query that is called as a stored procedure to return the results as a resultset: CALL Demo.Test_GetOwnersWithPetDetails() The GetOwnersWithPetDetails method is in the class Demo.Test and information about how to write a custom class query can be found in the Caché documentation. Class Demo.Test extends %RegisteredObject { /// Create a custom query to handle getting all pet info /// Assume maximum of three pets per PetOwner Query GetOwnersWithPetDetails() As %Query(CONTAINID=1,ROWSPEC="ID:%Integer,Name:%String,State:%String,PetType1:%String,PetSize1:%String,PetType2:%String,PetSize2:%String,PetType3:%String,PetSize3:%String") [SqlProc] { } /// Query initialization code goes here ClassMethod GetOwnersWithPetDetailsExecute(ByRef qHandle As %Binary) As %Status { Try { Set sc = $$$OK // Use a cursor to a query of the PetOwner table to the fetch method // The cursor is a public variable! // Declare the cursor &sql(DECLARE DemoC1 CURSOR FOR SELECT ID FROM Demo.PetOwner ORDER BY ID) // Open the cursor &sql(OPEN DemoC1) Set qHandle="" } Catch (ex) { Set sc = ex.AsStatus() } Return sc } ClassMethod GetOwnersWithPetDetailsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer=0) As %Status [PlaceAfter = GetOwnersWithPetDetailsExecute ] { Try { Set sc = $$$OK &sql(FETCH DemoC1 INTO :ownerID) // Assume maximum of three pets per PetOwner If (SQLCODE = 0) { // Initialize pet variables in case some are null Set PetType1 = "" Set PetSize1 = "" Set PetType2 = "" Set PetSize2 = "" Set PetType3 = "" Set PetSize3 = "" // Open the PetOwner with id from the cursor #dim owner As Demo.PetOwner Set owner = ##class(Demo.PetOwner).%OpenId(ownerID) Set Name = owner.Name Set State = owner.State Set numPets = owner.Pets.Count() #dim pet As Demo.Pet If (numPets>=1) { Set pet = owner.Pets.GetAt(1) Set PetType1 = pet.Type Set PetSize1 = pet.Size } If (numPets>=2) { Set pet = owner.Pets.GetAt(2) Set PetType2 = pet.Type Set PetSize2 = pet.Size } If (numPets=3) { Set pet = owner.Pets.GetAt(3) Set PetType3 = pet.Type Set PetSize3 = pet.Size } Set Row = $ListBuild(ownerID,Name,State,PetType1,PetSize1,PetType2,PetSize2,PetType3,PetSize3) Set AtEnd = 0 } ElseIf (SQLCODE = 100) { Set Row = "" Set AtEnd = 1 } Else { Set sc = $System.Status.Error(5001,"SQL fetch error: "_SQLCODE) Quit } } Catch (ex) { Set sc = ex.AsStatus() } Return sc } ClassMethod GetOwnersWithPetDetailsClose(ByRef qHandle As %Binary) As %Status [PlaceAfter = GetOwnersWithPetDetailsExecute ] { Try { Set sc = $$$OK &sql(CLOSE DemoC1) } Catch (ex) { Set sc = ex.AsStatus() } Return sc } }