Union with SQL

[JKas]

Hello,

I created a Tabular SQL query and then created a 2nd Tabular Model query. Both have one field in common. I want to join the queries. I unlocked, used the data>master/detail relationship. However, when I went to add the 2nd query as detail, it does not appear. Should I be able to join two different query types (TAB SQL to Tab Model)?

Thanks

p.s I've created other joins using Master/Detail joins that worked fine.

 

[mfgf]

Hi JKas,

To use a Master/Detail relationship you're normally looking at a situation where the detail query is embedded within the master query on the page - for example, the master query drives a list, and the detail query drives a crosstab within the list. There's no problem in one or both of the queries being fed by a Tabular SQL object. I have done this successfully a couple of times.

Best regards,

MF.

 

[unarayan]

I have a very simmilar situation. I created 2 query objects (ie query 1 and query 2) both contain a tabular object. Now I need to join both the query ojects based on a common field how do I do that?

 

[Cogno]

I have had to learn the hard way that this is actually not that difficult to do. However formating becomes a pain at times.

Open a blank report, create your two queries, then insert a list into your blank report. (Let it auto fill for the purpose of this test) Now add another List INSIDE of that list choosing your detail query. By doing a list within a list you now have the option to do a master detail relationship. Just click on the master/detail under data and choose your master (query 1), then choose if you are doing a filter or paramertized query. Now you have the option to show the relationship between the two.

If need be I try to creat a section with the first query so that I have a better formating option instead of two list.

Hope that isn't more confusing.

 

[unarayan]

Awesome, that worked the way I wanted it to.

Now can you elaborate more on formatting. I sure my master query and detail query will always have a one to one relationship and I want it to look like a simple list query, can you tell me how to do this.

I'm not sure I understand you when you say create a section instead of 2 lists.

 

[dmunson]

This is a lot easier, and faster, if you just put them into a single tabular model. The master detail might run multiple queries depending on the RDBMS platform.

Try this
1. Create a new query, add a tabular model.
2. Add both of your queries as children of the new tabular model.
3. Add a filter to the top tabular model joining the two fields in each of the child models, this will serve as the join.
4. Bring all of the fields you need, from both tabular models into the top tabular model.
5. Enable cross-product joins on the Query (not the tabular model) when doing this as reportnet does not recognoze your filter as a join.

Important!!!
Make sure all tabular models have names or Reportnet gets confused and thinks it is doing a circular reference.

Now you have a single tabular model with data from both queries. If they are from separate datasources Reportnet will do the join locally, if it is from a single relational source it should pass the entire sql statement to the database server allowing it to do the join on two derived tables. We have done this sort of process up to 8-10 levels deep. It also works with tabular references for creating a more encapulated query structure on the really complex reports.

 

[dslaster]

dmunson, I tried what you suggested. I am on CRN, and it wouldn't let me add queries 1 and 2 as children to query 3. Am I supposed to add the tabular models from 1 and 2 to 3? If not, how do I add the queries? When I go to the report page, it lets me add the facts from query 1 to the report but not from query 2, even though the query 2 facts are data items in query 3. Thanks for any help.

 

[dmunson]

Hi dslaster,
Think of a tabular model as the "Data layer" of your query. Tabular models will be converted to one or more SQL statements which will be sent to the database. It uses Derived tables (or derived queries) to represent the dindividual tabular models.

Think of a BIQuery as the "Presentation layer" of your query. This is the point where CRN owns the data and the database is for the most part, out of the picture.

Data can only be added to the UI from a BIQuery, not a tabular model.

You can only add one tabular model to a BIQuery.

You can add as many child tabular models as you would like to a parent tabular model.

As far as I know you can nest tabular models as far as you would like.

It should look something like this:

BIQuery <--Contains all columns in Tabular Model 1
|
+-Tabular Model 1 <--Uses a filter (2.id=3.id) as a join
|
+-Tabular Model 2 <-- Parent data
|
+-Tabular Model 3 <-- Child date


I hope that helps a little.