Recursion in a WebI query

[Former Member]

Hi everyone,

I would like to know how to reproduce that kind of SQL in a WebI report:

SELECT

pp.project_id,

pp.name as project_name,

pp2.project_id as project_id_of_model,

pp2.name as model_name

FROM

PA.PA_PROJECTS_ALL PP,

PA.PA_PROJECTS_ALL PP2

WHERE pp.created_from_project_id = pp2.project_id

It's kind of a recursion where I want to know what is the name of the model of project from which are created projects. As these models are projects themselves, I have to read the same table twice.

I have tried 2 queries, sub-queries, merging dimensions etc. and I can't get the results in one block. I would like to merge pp.created_from_project_id with pp2.project_id but WebI doesn't allow me to do so.

Does anyone has a clue to help me?

Thanks and have a good day.

[Former Member · Accepted solution]

If this is going to be an ongoing requirement, why not create a view in your database, then use that view in your universe?

[Former Member · Accepted solution]

Hi John,

I have tought of that but since my universe is based on Oracle Financials, I already have 3000 objects in it and I want to limit that, so I want to reuse data as much as possible.

If someone knows how to read the same table twice in a report, I will greatly appreciate it but I will keep your suggestion in mind because it's a simple way to resolve this issue.

Thanks and have a good day

Marc-André

[Former Member · Accepted solution]

Hi Marc-André

There are 2 ways to achieve it . But you need to perform these operations on the universe.

1. drag the table twice in the universe and join it on the primary key

2. drag one table and then create a derived table by selecting call columns from the same base table

once the universe is created the query with self join will automatically be created with a self join.

then do whatever manipulations you want to do on the reports.

unless you want to write a custom query directly in a report - which is not recommended.

Hope it solves your purpose.

Bernard.

[Former Member · Accepted solution]

Hi Marc-Andre,

I think the mergining is the right way to go.

Can you give more detail on the 2 queries, ie what fields you put in each

Did you have auto-merge on? Where you getting an error when you tried to merge or was it just that that the dialog was greyed?

Regards

Alan

[Former Member · Accepted solution]

Thank you everyone,

Since I had to get a result very fast, I have created a view.

If I have time, I will try the recursion and let you know if I make a success of it.

Have a good day.

Marc-André