New post
Question
· May 14, 2019

Mapping Globals To Classes - Variable Number of Subscripts

#Globals #SQL #Caché

I am trying to create an ODBC class that includes all of these as records, but I don't see how I can since the first record has 3 subscripts, the rest have 4 subscripts:

 

Here is my current storage definition:

<Storage name="Test">
<ExtentSize>100000</ExtentSize>
<SequenceNumber>25</SequenceNumber>
<SQLMap name="Test">
<Data name="ModificationDate">
<RetrievalCode> set {ModificationDate}={L4}</RetrievalCode>
</Data>
<Data name="Operator">
<Delimiter>":","|"</Delimiter>
<Piece>36,2</Piece>
</Data>
<Data name="CreationDate">
<Delimiter>":",","</Delimiter>
<Piece>24,1</Piece>
</Data>
<Data name="CreationTime">
<Delimiter>":",","</Delimiter>
<Piece>24,2</Piece>
</Data>
<Data name="TestField">
<Delimiter>":"</Delimiter>
<Piece>16</Piece>
</Data>
<Global>^DMHIS</Global>
<Subscript name="1">
<Expression>{Account}</Expression>
</Subscript>
<Subscript name="2">
<Expression>{PGC}</Expression>
<Invalidcondition name="1">
<Expression>{L2}'="P"</Expression>
</Invalidcondition>
</Subscript>
<Subscript name="3">
<Expression>{Entity}</Expression>
</Subscript>
<Subscript name="4">
<Expression>{DateTime}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Test</StreamLocation>
<Type>%CacheSQLStorage</Type>
</Storage>

Discussion (12)0
Log in or sign up to continue
Robert Cemper · May 14, 2019

In fact, I see not 1 but 2 tables that share the same global.

so it might be easier to have 2 separated definitions one with and the other without the  4th subscript (some $h)

for access with SQL you than can use a UNION to assemble them. like:

SELECT * FROM (
     SELECT Account, PGC, Entity, ' ' as DateTime, ..fields... FROM TAB1 WHERE <some condition >
     UNION ALL
     SELECT Account, PGC, Entity, DateTime, ..same as above... FROM TAB2 WHERE <same condition >
) order by 1,2,3,4

or  you decide to add the missing subscript in your global to have a common structure

6 0
Keith Davis · May 15, 2019

I hate to be a bother, and I agree, sounds like 2 classes would be more appropriate, but I've yet to create a View in this system (I'm fluent in MySQL, FYI), and frankly, I don't like views (been burned on performance issues too many times). However, any way you could show me the basics of both (2 classes with a view and 1 class), just so I understand better how this works. I know that's a lot to ask, but I've read a lot of docs, and some aspects I still do not understand.

If not, no big deal. I probably won't use the view because we are mirroring these "tables" into MySQL anyway and prefer to keep that part simple - 1 ODBC class = 1 MySQL table.

Also, and don't hit me, but we are on a very old version of Cache (and no way to fix that, blame our vendor).

Cache for Windows (x86-64) 2013.1 (Build 446) Tue Apr 23 2013 12:10:38 EDT

0 0
Brendan Bannon · May 15, 2019

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.

1 0
Brendan Bannon · May 15, 2019

Keith

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.

The Main trick here is to make understand that each subscript level is made up of 3 different parts that you can change or reference: 

     1) Data Access Expression and Data Access Variables:  This is the global and subscripts to be used at this level by the Next code, for example, you could have 3 levels that loop over 1 global and then at level 4 switch to a different global and only use 2 of the subscript values....  Data Access Variables are variables you can set at a given level before the Next code is executed.  You can use them at this level or any higher level and you can set them to values from lower levels.

     2) Next Code:  This is the code to get from one value row to the next in a table.  This could be the default $ORDER() code that is generated for a simple Subscript, or it could be override code you provide.

     3) Invalid Conditions:  If you provide Next Code at a given level then you should also provide Invalid Conditions.  This is a test you can use to see if you have a valid row when the subscript value is provided as a constant instead of needing to loop to find the next one.

For your class I reference the Data Access Expression at Subscript Level 3 {3D} in the data mapping.  This would be the global with the subscripts defined from the 2 lower Subscript levels:  ^Mapping({L1},{L2})

OK so that was a lot of details you did not ask for, but just in case someone wanted to understand all the gory details I figured I would write them down.

This xml file has the different classes I created:  Examples

0 0