New post
Question
John Bland · 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

0
0 330
Discussion (5)1
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
John Bland · Jul 25, 2019

Danny,

   In the second SQL,

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

I do not get the imdesc data.  The field is in the grid, but no data.  This is on the Management portal executing SQL.

       John

0 0
Danny Wijnschenk · Jul 26, 2019

Hi John,

Can you show me your class definitions, because when i execute the second SQL on my classes and some random data, it will show the results i expect :

(maybe you need to re-index your tables)

0 0
Eduard Lebedyuk · Jul 26, 2019

Also check that PhysicalRecord->ItemRecord  is not null

0 0
Brendan Bannon · Aug 15, 2019

I agree with Danny.  I would want to see the classes and the Show Plan for the 2 queries.

Did you define any indices in the classes and not populated them?

0 0