If you have flexibility with the schema definition then there are certainly other models that would make a query such as this simpler.

With an array collection that is stored as a serialized list you will have to either write a procedure, define some computed properties, or you will have to use some of the InterSystems collection extensions that may not work very well with collections of objects.

Our projection of serial types (classes that extend %SerialObject) uses a "flattening model" and we don't use that for serial collections stored as "listnode" structures. We do use it in the specific case of an array of serial objects stored using a "subnode" structure - the default.

I know this all sounds a bit scary but it doesn't need to be. When you get back to this we can walk through the options.

If the default array projection for PET.ImageStudy.Injection is used then there will be an SQL table projected from the array of objects. By default, the name of this table will be PET.ImageStudy_Injection. This projected table will define a column referencing the PET.ImageStudy table. I believe this column will be named ImageStudy. There will also be a column named Injection_RadioTracer in this table and that column will be a reference to the RadioTracer table.

If the array, Injection, in the PET.ImageStudy class is not stored using defaults then this becomes a more complex problem - still solvable but more difficult. 

Let me know if array storage defaults are used and then I can help compose a query - or perhaps you can do it with the information I provided above. You can verify this information using the System Explorer->SQL to view the table definitions.

-Dan

I should update the CSV and CSV2CLASS utility procedures to take advantage of table-valued functions. Then you could select JSON_ARRAYAGG() from CSVTVF(<arguments go here>). Of course you would have to supply the constructor for the objects, probably using JSON_OBJECT.

Have you considered using $xecute?

Version:1.0 StartHTML:00000092 EndHTML:00001371 StartFragment:00000172 EndFragment:00001337 CachÙ Studio clip   Class User.Exec [ Abstract ]
{

ClassMethod test(STATUS = 1) As %String
{
set greeting = $xecute("(STATUS) { return $S(STATUS:""HELLO"", 1:""GOODBYE"") }",STATUS)
return greeting
}

}

and here is this code in action:

PANTHER:XEP:DEV>w ##class(Exec).test()

HELLO

PANTHER:XEP:DEV>w ##class(Exec).test(0)

GOODBYE

As you have already discovered, there is no opportunity for a user to change the collection type class. That class is determined by the compiler when processing LIST and ARRAY keywords. This assignment, indicated by the compile-only keyword of RUNTIMETYPE, occurs during inheritance resolution and it cannot be overridden by the user. Your solution of coercing the RUNTIMETYPE using a method generator is not completely correct even though the runtime behavior seems correct. The problem with using the method generator is that is runs late in the compile cycle and the modification you make will not be processed by compiler actions that have already completed by this time. Your changes - simple addition of new methods - will probably work as you expect as long as those methods don't require any additional compiler processing. 

A user has only two ways to add methods to a class member - property in this case. The first and easiest is through the use of the declared type class. Internally this is cPROPtype (you have already discovered this I'm sure ;) ). Methods inherited from the datatype class (actually these are "datatype generator" classes) are combined with the property to produce the datatype implementation. These methods are typically not instance methods of the containing class but one could say they are instance methods of the generated datatype.

The second way for a user to inject methods into a class member runtime is by overriding the member super class. In the old days this was referred to as the property class but that definition is expanded to include other member types such as query, index, and so on. Methods inherited by a member from the member supertype class are typically instance methods of the containing class. For a property these methods include Get and Set. There are two keywords that a user can define - MEMBERSUPER and PROPERTYCLASS.

Both mechanisms for adding methods to a member's runtime produce what we call "composite methods". That doesn't really mean anything other than the name of the method which is composed of the member name and the inherited method name. There is an internal difference but that has little impact on runtime behavior. Composite methods look very much like normal methods but sometimes confuse users because it seems there should be a dot in the middle! For example, direct invocation of the Name property's Set method is

    do oref.NameSet(value)

This could be confusing as it seems logical to use

    do oref.Name.Set(value)

It is possible for a user to define a property super class containing method generators that will produce composite member methods.  This is not a simple process but it can be done. If this interests you then I can provide more details.

What I would recommend is an instantiable helper class. You can construct an instance of the helper class, passing in the collection instance. Methods in the helper class then operate on that instance. This is the model we use for iterators operating on instances of %Library.DynamicAbstractObject. 

At one time - in the pre-2.0 days of Caché Objects - we did discuss this but decided to settle on the implementation we currently have. The landscape is much more complex these days and there is a greater chance of a composite method name collision or parameter name collision. Not too long ago, I had to redesign the index constraint projection (primary key, unique) and referential constraint projection (foreign key) because we had name collisions.

There is a short answer to this and a long explanation. The short answer is that the warning is accurate and you might experience problems if you use the same name for two class members. When defining classes myself, I keep method names and property names distinct, mostly by using nouns and verbs. For indices, I prefix the name with a letter indicating the index type.

The reason why you should avoid non-unique names is simple. Most member types inherit methods from what I refer to as the member super tree. The member super tree has an abstract top node with two subnodes, the member super class and the declared type class. The member super class for properties is often called the "property class" and the declared type class is the class the author defines as the type. Methods inherited from the member super tree are implemented in the resolved member runtime as a composite name formed from the member name and the method name. For a property named 'Foo', you might see methods in the class runtime named 'FooSet' and 'FooGet'.

If two members share a name it is possible that a composite method name will conflict between the two member types. We try to avoid this when implementing our member super classes and our type classes but it is still possible to encounter runtime method name conflicts.

This method call is not part of any release but, as others have mentioned, you can use %Net.Http to submit a request and receive a response. I can help you write that request. How does this look?

USER>set matrix = ##class(<your classname goes here>).GetJSON("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=40.6655101,-73.89188969999998&destinations=40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.659569%2C-73.933783%7C40.729029%2C-73.851524%7C40.6860072%2C-73.6334271%7C40.598566%2C-73.7527626%7C40.659569%2C-73.933783%7C40.729029%2C-73.851524%7C40.6860072%2C-73.6334271%7C40.598566%2C-73.7527626&key=<you api key goes here>",{"SSLConfiguration":"<your SSL Configuration goes here>"})

USER>write matrix.%ToJSON()

{"httpStatus":"200","message":"HTTP/1.1 200 OK","content":{"destination_addresses":["67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","585 Schenectady Ave, Brooklyn, NY 11203, USA","66-0-66-26 103rd St, Rego Park, NY 11374, USA","931-947 N Village Ave, Rockville Centre, NY 11570, USA","300-448 Beach 19th St, Far Rockaway, NY 11691, USA","585 Schenectady Ave, Brooklyn, NY 11203, USA","66-0-66-26 103rd St, Rego Park, NY 11374, USA","931-947 N Village Ave, Rockville Centre, NY 11570, USA","300-448 Beach 19th St, Far Rockaway, NY 11691, USA"],"origin_addresses":["566 Vermont St, Brooklyn, NY 11207, USA"],"rows":[{"elements":[{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"3.4 mi","value":5426},"duration":{"text":"19 mins","value":1136},"status":"OK"},{"distance":{"text":"8.5 mi","value":13740},"duration":{"text":"23 mins","value":1399},"status":"OK"},{"distance":{"text":"15.9 mi","value":25546},"duration":{"text":"29 mins","value":1755},"status":"OK"},{"distance":{"text":"13.2 mi","value":21296},"duration":{"text":"34 mins","value":2040},"status":"OK"},{"distance":{"text":"3.4 mi","value":5426},"duration":{"text":"19 mins","value":1136},"status":"OK"},{"distance":{"text":"8.5 mi","value":13740},"duration":{"text":"23 mins","value":1399},"status":"OK"},{"distance":{"text":"15.9 mi","value":25546},"duration":{"text":"29 mins","value":1755},"status":"OK"},{"distance":{"text":"13.2 mi","value":21296},"duration":{"text":"34 mins","value":2040},"status":"OK"}]}],"status":"OK"}}

And with a little work, this can also be invoked as an SQL function:

That phrase is not a reference to a special type of class, instead it refers to the reason the class is added to the set of classes to be compiled. For example, if you compile Sample.Employee it becomes a "direct class" in the set of classes to be compiled. The compiler evalutes each class in the set of classes to produce an "expanded set of classes". In this case, Sample.Person would be included as an "expanded class". HTH.

To set a property whose type class is a serializable class (extends %Library.SwizzleObject - streams, serial classes, persistent classes) you need to call the property's SetObject or SetObjectId method if you wish to set the value to a serialized value (id value often). I think that in your example, tvar1 is holds an ID value of the referenced ZenCrm.Relationtypes class? If so, then this should work:

do RelationMatrix.RelationAIDSetObjectId(tvar)

The ID is assigned by %SaveData() and %OnBeforeSave is invoked before %SaveData is called. For a new object the ID value will not be reliable until after %SaveData has returned. I do not know whether or not this is documented explicitly.

I defined this simple class:

 Class Community.IdBeforeSave Extends (%Persistent, %Populate)
{

Parameter USEEXTENTSET = 1;

Parameter DEFAULTGLOBAL = "^C.I";

Property Name As %String;

Property DOB As %Date;

Property SSN As %String;

/// This callback method is invoked by the <METHOD>%Save</METHOD> method to 
/// provide notification that the object is being saved. It is called after 
/// the object's data has been successfully written to disk.
/// 
/// <P><VAR>insert</VAR> will be set to 1 if this object is being saved for the first time.
/// 
/// <P>If this method returns an error then the call to <METHOD>%Save</METHOD> will fail.
Method %OnAfterSave(insert As %Boolean) As %Status [ Private, ServerOnly = 1 ]
{
set id = ..%Id()
set ^C.S($increment(^C.S)) = "OnAfterSave, insert = "_$select(insert:"'true'",1:"'false'") _ ", ID = " _ $select(id'="":id,1:"<null>")
Quit $$$OK
}

/// This callback method is invoked by the <METHOD>%Save</METHOD> method to 
/// provide notification that the object is being saved. It is called before 
/// any data is written to disk.
/// 
/// <P><VAR>insert</VAR> will be set to 1 if this object is being saved for the first time.
/// 
/// <P>If this method returns an error then the call to <METHOD>%Save</METHOD> will fail.
Method %OnBeforeSave(insert As %Boolean) As %Status [ Private, ServerOnly = 1 ]
{
set id = ..%Id()
set ^C.S($increment(^C.S)) = "OnBeforeSave, insert = "_$select(insert:"'true'",1:"'false'") _ ", ID = " _ $select(id'="":id,1:"<null>")
Quit $$$OK
}

and then populated it with 5 objects. This is the result:

LATEST:USER>zw ^C.S

^C.S=10

^C.S(1)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(2)="OnAfterSave, insert = 'true', ID = 1"

^C.S(3)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(4)="OnAfterSave, insert = 'true', ID = 2"

^C.S(5)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(6)="OnAfterSave, insert = 'true', ID = 3"

^C.S(7)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(8)="OnAfterSave, insert = 'true', ID = 4"

^C.S(9)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(10)="OnAfterSave, insert = 'true', ID = 5"

HTH,

Dan

Yes, this should work but you are missing a step - you must execute the statement. Dynamic SQL allows for a statement to be prepared once and executed many times. A prepared statement is executed, returning a statement result. You can provide different parameter values for each execution.

set result = tStatement.%Execute()

Alternatively, you can do this in a single comment -

set result = $system.SQL.Execute("UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)")

-Dan

SYSTEM is not a valid filter. That keyword allows the class compiler to organize classes into compile 'groups' so that a class that may depend on another class at compile time is guaranteed to have the dependency resolved.

It isn't an indicator that the class originated in %SYS/%CACHELIB.

 Class User.DQ Extends %SQL.CustomQuery
{

Property id As %Integer;

Property lastName As %String;

Property firstName As %String;

Property age As %Integer;

Property ptr As %Integer [ Private ];

Property atEnd As %Boolean [ Private ];

Parameter SQLNAME As String = "DQ";

Method %OpenCursor() [ Private ]
{
try {
// Let's set up some temporary data
set ^CacheTempDQ(1) = $listBuild("Smith","John",42)
set ^CacheTempDQ(2) = $listBuild("Jones","Quincy",80)
set ^CacheTempDQ(3) = $listBuild("Wilson","George",71)
set ^CacheTempDQ(4) = $listBuild("Garcia","Andrew",32)
set ^CacheTempDQ(5) = $listBuild("Smoak","Felicity",24)
set ^CacheTempDQ(6) = $listBuild("Partridge","Audrey",32)
set ^CacheTempDQ(9) = $listBuild("Williams","Andrie",92)
set ^CacheTempDQ(10) = $listBuild("Orr","Robert",62)
set ^CacheTempDQ(11) = $listBuild("Moon","Lila",21)
set ^CacheTempDQ(120) = $listBuild("Lola","Elleoh",67)
set ..ptr = ""
set ..atEnd = 0
set ..%SQLCODE = 0
set ..%Message = ""
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
}

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
{
try {
set ..ptr = ""
set ..atEnd = 0
kill ^CacheTempDQ
set ..%SQLCODE = 0
set ..%Message = ""
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
}

Method %FetchCursor(ByRef sc As %Library.Status = {$$$OK}) As %Library.Integer
{
try {
if '..atEnd {
set ..ptr = $order(^CacheTempDQ(..ptr),1,data)
if ..ptr {
set link = ""
set ..lastName = $list(data,1)
set ..firstName = $list(data,2)
set ..age = $list(data,3)
} else {
set ..atEnd = 1
set ..%SQLCODE = 100
}
}
} catch exception {
set ..%SQLCODE = exception.AsSQLCODE()
set ..%Message = exception.AsSQLMessage()
}
return '..atEnd
}

}

Then execute this statement:

select * from DQ()

or 

select * from DQ() where lastName %STARTSWITH 'S' order by age DESC

Hi Chris,

Good question! This is a poorly understood area and there is much to say about it. I'll try to be brief. Feel free to follow up.

Your new property falls into the category of properties commonly referred to as 'derived columns' in other products. We support two different flavors - 'always computed' and 'triggered computed'. An 'always computed' property is indicated by the CALCULATED keyword. A 'triggered computed' property is 'NOT CALCULATED' although this is the default if you don't specify it. A triggered computed property can be TRANSIENT or stored (not transient - again, the default) and its value is triggered to recompute if the value of any property specified in the SQLCOMPUTEONCHANGE list of properties changes. In an object context, you need to be a aware that the value of a TRANSIENT property is directly settable unless you take precautions to prevent that.

The advantage to using SQLCOMPUTED/SQLCOMPUTECODE to derive a property value is that SQL and Objects seamlessly (almost) recognize the derived value and invoke the computation at the proper time. Overriding Get/Set methods can be used to derive property values but SQL will not recognize this as you have already discovered. 

SQL does not operate in an instance context. In other words, there is no oref available to SQL so SQLCOMPUTECODE must run in a class context and not in an instance (object) context. No oref. The way you have implemented your code is correct and, sorry to say, there isn't a cleaner way that I know about. 

There is one other thing you should know. When you open the object you may not actually get a new oref if that object was previously open in your process. That goes for any call to %Open([d]  - we cache the OID's with their active orefs and always return the existing oref from %Open if one is present in process memory. What does that mean? It means that you may see data in memory that has not committed to storage - Objects sees the data because of the in-memory context but SQL won't because it runs in an on-disk (storage) context. By opening objects you may produce a computed value that is different from the same data that is stored. There are ways you can avoid this problem, one being to store the derived value and trigger it to recompute on INSERT and UPDATE. This is easily defined by adding %%INSERT and %%UPDATE to the SQLCOMPUTEONCHANGE list of trigger properties.

Not so brief.

-Dan

Hi Peter!!

Your comment is correct of course. But think about the behavior and not the physical model. Everything that PARENT/CHILDREN relationships offers can be easily replicated using ONE/MANY with the only differences being the default storage model and the composite IDKEY that is required by PARENT/CHILDREN. With ONE/MANY you have the advantage of using system assigned integer ID's (frees you up to use bitmap indices - think product/invoice line perhaps) and you have more flexibility with referential actions. PARENT/CHILDREN requires ONDELETE=CASCADE. With ONE/MANY you have the option for ONDELETE=NOACTION/CASCADE... And, of course, if you with to establish existence dependency between the parent class and the child then simply add a REQUIRED constraint to the relationship whose cardinality = ONE.

Hope to see you at Global Summit!

Dan

I am not a fan of parent/children relationships. Think about what this really means. Kyle's comment about the data being nested beneath the parent object in the global is just the default physical storage model. The user has control over storage and can change that structure. But consider the logical behavior that PARENT cardinality brings. First, a relationship is like a foreign key - it is constrained by a referential constraint and it is subject to referential actions. Since this is a foreign key referencing the IDKEY of the parent class there is no need to define an ONUPDATE action (IDKEY values cannot be changed) but ONDELETE must be CASCADE. Why? Because PARENT cardinality establishes an EXISTENCE dependency on the parent. You can't change that unless you change the cardinality to ONE. Secondly, the IDKEY of the child class implicitly includes the parent relationship. This establishes an IDENTIFICATION dependency on the child class. You can't identify (the ID/OID) an instance of the child class unless you know the ID of the parent class. Furthermore, once a parent value is assigned and saved, you cannot change the value of the parent property. To do so would identify a different object and it isn't allowed. So - PARENT defines existence and identification dependencies.

To summarize PARENT/CHILDREN behavior:

1. Existence dependency;

2. Identification dependency;

3. Physical storage default is nested (artificially creating an implicit ONDELETE CASCADE behavior at the physical level - IMO this is not a good thing!)

And think about other ways to achieve the same thing using ONE/MANY or even a simple foreign key:

1. Existence dependency - make the relationship in the child class (or property if using fkey) REQUIRED;

2. Identification dependency - define the IDKEY and add the relationship with cardinality = ONE to the IDKEY;

3. Physical storage model nested - I don't recommend this model for ONE/MANY. 

The advantages of ONE/MANY:

1. The user has control over dependencies;

2. More ONDELETE/ONUPDATE options;

3. The option to base the IDKEY on a positive integer value, allowing the use of bitmap indices;

What does a relationship do for you that a foreign key does not? One thing. It maintains the relationship in memory when either side of the relationship is in memory (swizzled). This can be a good thing. It can also be a bad thing if there are a large number of related objects in the n-cardinality side.

Caché Objects persistent classes project to SQL as tables. Caché Objects relationships are binary - a relationship must have a defined inverse relationship in the related class. We only support relationship/inverse relationship cardinality pairs of one:many and parent:children (a special case of one:many where the parent is implicitly required and is also implicitly part of the IDKEY). A Caché Objects n-cardinality relationship (children, many) is transient in the container and there is no projection to SQL. A 1-cardinality relationship is stored and projects to SQL as a column that is constrained by a foreign key constraint. A simple query can be used to retrieve the contents of the unprojected n-cardinality relationship. Consider two tables, A and B, related by properties b and a. A.b is of type B and specifies cardinality = many.  B.a is of type A and specifies cardinality = one.

The value of A.b can be determined by this query:

select %ID from B where B.a = ?

and specify '?' as the A.%ID value. In fact, the internal relationship implementation uses a very similar query to populate A.b on %Open.

Hi John,

Great question! There is a lot of misinformation floating around about the general topic of CALCULATED, TRANSIENT, SQLCOMPUTED, etc. I'll try to clear the air and lay a clean foundation. First, let's discuss CALCULATED and TRANSIENT.

Each property defined in a class is accessed by a "getter" and a "setter", implemented as <property>Get and <property>Set methods respectively. In most common cases these accessor methods are trivial and are implemented at a very low level in Caché simply retrieve or store a value in the instance memory allocated to the property. In fact, the <property>Get and <property>Set method generators depend on instance memory. If no instance memory is allocated then the method generators have no clue where to retrieve the property's value from or where to set (store) the property's value. The allocation of instance memory is controlled by the CALCULATED keyword. If CALCULATED is true then no instance memory is allocated and the only way to access the property value is to override the accessor methods. Commonly, only the <property>Get method is overridden. We refer to such a property (CALCULATED with overridden Get method) as a "derived property". A CALCULATED property is not implicitly settable but, in theory, such a property can be set if the <property>Set method is implemented. I don't know offhand if the theory works in practice.

Now on to TRANSIENT. TRANSIENT simply means that the property is not stored when the containing object is saved. For a subclass of %SerialObject it means the property is not part of the serialized state produced by %GetSerial. There is instance memory allocated for a TRANSIENT property, assuming the user hasn't also defined it as CALCULATED. BTW, CALCULATED implies TRANSIENT but the reverse is not true. Since instance memory is allocated to a TRANSIENT, NOT CALCULATED property, the system does generate accessors (Get and Set) and such a property is implicitly settable. 

Users can override the Get/Set accessors to inject custom behaviors for any property, CALCULATED or not, but doing so directly causes problems for SQL. (Property accessor methods run in an instance context and SQL does not.) Caché provides a mechanism for deriving a property value that can be used outside of an instance context. That mechanism is triggered by SQLCOMPUTED and SQLCOMPUTECODE. I won't go into details of how to define SQLCOMPUTECODE here.

If a property is defined as CALCULATED and SQLCOMPUTED then we refer to that property as "always computed", meaning that every <property>Get reference will cause the SQLCOMPUTECODE to be executed (more on that in a bit) to derive the current value of the property. 

If the property is defined as NOT CALCULATED and SQLCOMPUTED then we refer to that property as "triggered computed" and its value will only be derived when certain conditions are true. First, if the current value of the property is NULL then the SQLCOMPUTECODE is called to derive the current value. Second, if the value of any property in the SQLCOMPUTEONCHANGE list of properties is changed then the SQLCOMPUTECODE is called to derive an updated value. Finally, there are two special tokens that can be specified in SQLCOMPUTEONCHANGE - %%INSERT and %%UPDATE. These tokens will trigger the SQLCOMPUTECODE to derive an updated value for the property when inserting (%%INSERT)  a new instance into the extent of the class (or a new row in the SQL table) or updating (%%UPDATE) and existing instance or row.

That's the foundation. 

Determinism. The function used to derive a value is only deterministic if that function returns the same value each time it is invoked with the same inputs. (if a = b then f(a) = f(b)). A property that is NOT CALCULATED + TRANSIENT is implicitly non-deterministic in an instance context. A CALCULATED property can be deterministic but it depends on the implementation of the function to derive the value. A property whose value is derived by overriding Get, Set or both is implicitly non-deterministic in an SQL context. (this concept is similar to third and fourth normal forms in relational systems). A simple non-deterministic function is now() implemented in Caché as "return $h". A more complex example would be a function that returns a value retrieved from another table. That function is non-deterministic because the value retrieved can be updated at any time, that is, passing the same input argument to the function multiple times can produce different results.

And the short answer to your question - if you define derived properties in your class for the purpose of simplifying SQL queries then make certain you understand the implications of non-deterministic derivation. Not all non-deterministic derived values are bad. Take Age() as an example. If Age is computed based on current time then it is non-deterministic. That makes it a bad candidate for a NOT CALCULATED property but showing Age in a query is a good thing.

HTH,

Dan

Hi,

There is no capability to order the objects returned by the various iterator methods implemented by %Library.RelationshipObject. Your best alternative is to use SQL to select the related objects using a restriction similar to "WHERE Company = :companyID" and then to include explicit ORDER BY to define the desired ordering. Of course, this comes with some requirements - the set of related objects, Employee instance in this example, has to be saved to the Employee extent before you can use SQL to query them and also any changes made to those instances in memory will not be reflected by the query results. That is why I used the technique of querying using dynamic SQL and %ObjectSelectMode as it reduces the impact of in-memory changes.

It doesn't completely eliminate the "persistence" requirement for querying as  any newly related objects have to be saved before they can be queried. 

The only other possiblity is for you to iterate over the set of related objects and build your own sorted array.

Hope this helps!

Dan