User Answers



Here is a simple example I came up with that can show you the 2 different ways to do this.  As the comments in the class state the restriction for Mapping.OneClassParentChild is every parent must have at least 1 child as the IDKey for the rows is the Child row.  If you need to support parents without children this will not work.  I will need to think about how we might be able to do that if that is needed.  I would also push harder for the 2 classes and a view approach if that is needed.


Hi Keith


You can map anything!

the question is do you really want to and is it the best thing to do?

This looks like a classic Parent Child relationship that would be defined by 2 classes.  You could then create a view that would JOIN the 2 tables together and expose that view via ODBC and you would have what you want.


I can create a simple example that will show this data in one class, but I don't think it will add much value.




Your code does not match your class descriptions.  In your code you are using a property, Parent to do the child lookup, but there is no parent property defined in Example.Child.

If you truly want a Many to Many then you can't define a Parent property in the child, unless it is a list, and I am not recommending that!

Hi Jimmy

Just to make sure we are talking about the same thing:  in Cache the Primary Key is not always the ID Key.  I think you are really talking about the ID Key, used in the subscripts of the D global.  If I am correct then you have an index define on Year , Month, OrderNo with IDKey True.

From a Parent Child point of view having a compound IDKey is fine.  The IDKey of the parent will be something like "2018||11||1" and then the IDKey of the children will just add one more subscript:  "2018||11||1||child 1"

Hello I am trying to get a good example, but for now I will just try to give you a general description. As the Docs say this hint is used in a subquery. At compile time the optimizer has 2 options for dealing with a subquery: 1) Treat it as a black box, executing the subquery independently from the other query and then applying the results to the outer query 2) rewrite the query incorporating the subquery into the outer query as a JOIN and combining conditions from the subquery to the WHERE clause of the outer query.


Do you have an index on c_drugfull?

The most likely difference between the 2 queries above would be the first using that index and the second not using the index.  If the index has bad data that would explain the issue.


Of course there is a good chance your test from SQL Server would also use that index so I could be wrong.



You must be starting on a version that supports Frozen Query Plans.  In order to freeze a plan the query compiler needs to save some internal information so it can regenerate the same code on recompile.

2016.2 is the first version to support frozen plans.


I think what you want to do is write this a a JOIN without the subquery and then use %INORDER to force the compiler to start with the account table.  then we should pick the Type index and everything should work the way you want.



from %INORDER Account  JOIN Transaction 
on Account.Id = Transaction.Account  




Not that I have seen a lot of outbound adaptor code, but I have never see TSQL and a query sent over the wire like this.


If you just send the SELECT does it work?


are you getting any info back from the execute you are logging?


$$$LOGINFO("tSC = "_tSC)


Thanks for sending the class to me Yaniv.  


Life is always easier when you don't have to guess what is happening. 


The 2 options list above are not really options, but rather the steps you should take when defining any type of index for Cache SQL Storage.  Defining the Index def helps us correctly report info to external databases, but it is the map in the storage that the query optimizer is looking for, so #1 is required for all types of indices.  

Here is the property and index