New post
Question
· Jul 24, 2019

Using Relationships

#Caché

In Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2018.1.2 (Build 309U) Mon Mar 4 2019 15:07:46 EST

I have a master record that has a relationship to a table with multiple transactions, and the transaction table has a relationship to another transaction table with multiple other transactions.

IE Item to Physcal to Physical Report

Item Master:

Relationship PhysicalRecord As User.pytrn [ Cardinality = many, Inverse = ItemRecord ];

First transaction table

Relationship ReportRecords As User.pyrpt [ Cardinality = many, Inverse = PhysicalRecord ];

Relationship ItemRecord As User.immst [ Cardinality = one, Inverse = PhysicalRecord ];

Second Transaction table

Relationship PhysicalRecord As User.pytrn [ Cardinality = one, Inverse = ReportRecords ];

Everything compiles with no issues and all records have the appropriate data (IDs).

    In SQL why does

select pyrpt.pypqty,PhysicalRecord->pypqty as pypqty1, PhysicalRecord->ItemRecord->imdesc from SQLUser.pyrpt

return the imdesc field from the master table, but

select pyrpt.pypqty, PhysicalRecord->ItemRecord->imdesc from SQLUser.pyrpt

 does not?  Why do I have to force swizzling to occur for the two layer access to operate.

    John P. Bland

Discussion (5)0
Log in or sign up to continue
Danny Wijnschenk · Jul 25, 2019

Hi John,

I tried to create the classes you are describing, and they seem to give the correct results in the two SQL statements.

The two query plans are also giving the same access path.

Can you tell me what the outcome (data/error) of the second SQL statement is giving you ? 

I used following classes :

Class User.immst Extends %Persistent
{
Relationship PhysicalRecord As User.pytrn [ Cardinality = many, Inverse = ItemRecord ];
Property imdesc As %String;
}
Class User.pytrn Extends %Persistent
{
Relationship ReportRecords As User.pyrpt [ Cardinality = many, Inverse = PhysicalRecord ];
Relationship ItemRecord As User.immst [ Cardinality = one, Inverse = PhysicalRecord ];
Property pypqty As %String;
}
Class User.pyrpt Extends %Persistent
{
Relationship PhysicalRecord As User.pytrn [ Cardinality = one, Inverse = ReportRecords ];
Property pypqty As %String;
}
 
0 0