Nice clear description Benjamin.  This should be a big help for query performance.  It is such a common problem in support when we have to help customers deal with outliers.  Now the RTPC should do that work for them!

So there is a zip file attached to this article that contains a bunch of examples.  the Class Mapping.ChildPiece should be close to what you need.  the global for that class Parent Child looks like this:

/// ^Par(1)=Parent1\Child11#child12#child13
///      2)=Parent2\Child21#child22
///      3)=Parent3
/// 

and the mapping for the child is:

<SQLMap name="Map1">
<Data name="ChildData">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^Par</Global>
<RowReference>$P($P(^Par({L1}),"\",2),"#",{L2})</RowReference>
<Subscript name="1">
<Expression>{Mapping.ParentPiece.Sub1}</Expression>
</Subscript>
<Subscript name="2">
<AccessType>Piece</AccessType>
<Delimiter>"#"</Delimiter>
<Expression>{PieceCounter}</Expression>
<Invalidcondition name="1">
<Expression>$P(^Par({L1}),"\",2)=""</Expression>
</Invalidcondition>
</Subscript>
<Type>data</Type>
</SQLMap>

If you can get your working let me know and I can make an example for your global.

Brendan

Hi Limyandi

I am not a fan of creating List of Objects as it make it hard to query the data.  I would rather expose this data as a child class somehow so SQL can be used to look at the data.  I would look at doing something along the lines of what is in Mapping.ChildPiece.

But you ask for an ugly List of Lists so that is what I did. 

The Serial class is just a normal one:

Class Mapping.Address2 Extends %SerialObject [ ClassType = serial, ProcedureBlock ]
{

Property RoadName As %String(TRUNCATE = 1);

Property SuburbanName As %String(TRUNCATE = 1);

Property StreetNumber As %String(TRUNCATE = 1);

Storage Default
{
<Data name="Address2State">
<Value name="1">
<Value>RoadName</Value>
</Value>
<Value name="2">
<Value>SuburbanName</Value>
</Value>
<Value name="3">
<Value>StreetNumber</Value>
</Value>
</Data>
<State>Address2State</State>
<StreamLocation>^Mapping.Address2S</StreamLocation>
<Type>%Library.CacheSerialState</Type>
}

}

Then for the Person class I needed to write Retrieval code that would get the different address out of the global and put them into the Nested $LIST() that the class is expecting.

Class Mapping.Person2 Extends %Persistent [ StorageStrategy = NewStorage1 ]
{ Property Name As %String;

Property Addresses As list Of Mapping.Address2;

Property Sub1 As %Integer;

Index Master On Sub1 [ IdKey ]; Storage NewStorage1
{
<ExtentSize>100</ExtentSize>
<SQLMap name="Map1">
<Data name="Addresses">
<RetrievalCode>
      set string=$P(^Person({L1}),"|",2)
      for i=1:1:$L(string,"^") {
           set address=$P(string,"^",i)
           set $LIST({*},i)=$LISTFROMSTRING(address,"~")
      }
 </RetrievalCode>
</Data>
<Data name="Name">
<Delimiter>"|"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^Person</Global>
<Structure>delimited</Structure>
<Subscript name="1">
<Expression>{sub1}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SimpleS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}