How to get Value of Collection of Collection?

Question

Question

Dhaval Shah · Jan 17, 2019

#ObjectScript #SQL #Caché #Ensemble

Let Say I have
Class Carrier Extends %Persistent

Property Employee As Array of Class Employees

Class Employees Extends %SerialObject

Property Name As %String

Property Address As Array of Class AddressDetail

Class AddressDetail Extends %SerialObject

Property Street1 As %String

Property Street2 As %String

I can get value for Employee Name as Employee_Name.

But I want to Get Value of Address Street1 and Street2 of Class AddressDetail using SQL Query
How can I get that?

Discussion (3)1

Log in or sign up to continue

Matthew Giesmann · Jan 18, 2019

Hello Dhaval,

I don't believe that there is a clean way to access the property you are looking for, though you may be able to finagle the data you need in SQL with the $list function.

On the first array level (Employee), Cache projects the property as a table, allowing for easy SQL access. However, because Employee is not Persistent, it cannot project its address array property as a table. Any SQL access will have to deal with the $listbuild that the second level array is stored as.

If possible, I would make Employee a stand alone table, maybe as a Relationship rather than an array.

1 0

score 1 · Answer 1 · 2019-01-24T22:05:47-05:00

Thanks @Matthew Giesmann , You were correct, it was getting Difficult So we made Employee a Standalone Table and other changes as well, then we gatered data using SQL Query.

Thanks @Muhammad Awan as well, for quick help in the same.

score 0 · Answer 2 · 2019-01-25T09:47:07-05:00

Matthew Giesmann · Jan 25, 2019

Great! Glad to hear that worked.

0 0