Union in framework manager - where to apply it?

[rteruyas]

Good day guys
I'd like to know what's the best practice when working with UNION operators in framework manager.

Scenario (simplified)
My model has 3 layers Data, Business, Presentation.
In Data layer, I have these 3 tables:

Code Select Expand


Master_ClientA
--------------------
idClient
typeClient
Name
Address


Code Select Expand


Master_ClientB
--------------------
idClient
typeClient
Contact1
Contact2
Comment


Code Select Expand


Master_Transactions
------------------
idClient
typeClient
dateTransaction
amount


I'd like to have one Query subject for all Clients using UNION operator.
Q1: Should the new query subject go in data layer or in the business layer?

Right now, both Master_ClientA and Master_ClientB are joined to Master_Transactions.
I tried creating the UNION query subject "Master_ALL_Clients"
However, when I query something from Master_ALL_Clients in combination with Master_Transactions in RS, I get cross-joins not allowed...
I don't want cross joins in my model so my question is:

Q2: Should I use the union query subject to do the joins with the other tables from the data layer?

Question 3 and 4 are more theory than practice
Q3: Is there a better approach to use in this case?
Q4: Where can I find what the best practices are for situations like this

Thanks in advance!

[MFGF]

Good day guys
I'd like to know what's the best practice when working with UNION operators in framework manager.

Scenario (simplified)
My model has 3 layers Data, Business, Presentation.
In Data layer, I have these 3 tables:

Code Select Expand


Master_ClientA
--------------------
idClient
typeClient
Name
Address


Code Select Expand


Master_ClientB
--------------------
idClient
typeClient
Contact1
Contact2
Comment


Code Select Expand


Master_Transactions
------------------
idClient
typeClient
dateTransaction
amount


I'd like to have one Query subject for all Clients using UNION operator.
Q1: Should the new query subject go in data layer or in the business layer?

Right now, both Master_ClientA and Master_ClientB are joined to Master_Transactions.
I tried creating the UNION query subject "Master_ALL_Clients"
However, when I query something from Master_ALL_Clients in combination with Master_Transactions in RS, I get cross-joins not allowed...
I don't want cross joins in my model so my question is:

Q2: Should I use the union query subject to do the joins with the other tables from the data layer?

Question 3 and 4 are more theory than practice
Q3: Is there a better approach to use in this case?
Q4: Where can I find what the best practices are for situations like this

Thanks in advance!

Hi,

To union two tables or query subjects, you would need the number of columns and the data type of those columns to be the same in each. You appear to have different columns in Master_ClientA  and Master_ClientB? Can you explain how these can be Unioned if they are different structures?

If you have two tables of the same structure and you want to Union them, I'd always do this in the Data layer - in fact I'd put all my joins/unions etc here, to get the best chance of query minimization later on. I would select the two query subjects and use Actions -> Define Query Set to union them into a new query subject. This would then need to be joined to whatever other query subjects are required. It sounds like you haven't joined the results of your union to anything else from the description you posted - errors about cross-joins usually indicate missing relationships.

Cheers!

MF.

[rteruyas]

You appear to have different columns in Master_ClientA  and Master_ClientB? Can you explain how these can be Unioned if they are different structures?

Thanks for your answer MFGF
I had created two additional query subjects, filling the additional columns with null or 0 when needed.

_tempClientA
----------------
idClient
typeClient
Name
Address
Contact1
Contact2
comment

_tempClientB
----------------
idClient
typeClient
Name
Address
Contact1
Contact2
comment

Then I created the "Master_ALL_Clients" using UNION between these two query subjects
The relationships were not made using the "Master_ALL_Clients" but with each Master_ClientA and Master_ClientB, I think that was causing the cross-joins error. Will give it a try today. Thanks!