OUTER JOINS

[Former Member]

Hello,

In my universe I Have two tables joined as follows :

tableA LEFT OUTER JOIN tableB ON tableA.ColumnA = tableB.ColumnB

Whenever I create a query in Web Intelligence and I want to apply a filter in a field ( f.i Column C) of tableB

the following query is produced :

.
SELECT ..... 
FROM 
....
tableA    LEFT OUTER JOIN tableB   ON  tableA.ColumnA =  tableB.ColumnB
...

WHERE  tableB.CoumnC = 'x;

As you can see the LEFT OUTER JOIN becomes INNER JOIN.

I have the posiibity to use the Custom SQL tool ( WebI) and edit this query as follows :

 

SELECT ..... 
FROM 
....
tableA    LEFT OUTER JOIN tableB   ON  tableA.ColumnA =  tableB.ColumnB
                                                         AND  tableB.CoumnC = 'x;
...

however I would like to know how i can fix this through Universe Designer

Thank you

[PPaolo]

Hello

I don't know if it will work exactly as you expect in your situation but give a try to the workflow here below to see if it helps out.

There is a parameter in Universe Designer (from 6.5 I believe) which is called 'FILTER_IN_FROM'.

To set it up:

open your Universe

Go to the Universe parameters page and set:

ANSI92 to YES

FILTER_IN_FROM to YES

Save the universe (or export it) and try again your query.

The parameter's goal is to push all filters which usually go in the Where clause into the From clause.

The parameter is global: if you set it then all filters will be pushed into the From clause.

If you want to define the behaviour join by join then you should set only the ANSI92 to yes and leave the Filter_in_from to No.

Then you double click on a Join, select the "Advanced properties" and define when this join will host filters in the From clause.

Hope that it helps, please let us all know if it does.

regards

PPaolo

[Former Member]

Unfortunately no,

I tried both methods ( FILTER_IN_FROM to YES and ADVANCED PROPERTIES) , but whenverer i run the query the condition

tableB.CoumnC = 'x' goes after where