We are moving away from Hyperion Brio to TDP for some reports. The way that Brio worked was that, when putting a limit/filter in a field from a table that had an outer connection to the rest of the statement, the outer join would be taken into account (only apply filter if there is a field to be selected). In the diagram window of the query builder a filet/where clause entered in the column/field bar acts as a filter on the entire results.
I know how to fix this in the SQL statement (adding (+) after the fieldname, of defining a “custom” where clause formula). However,before teaching users that have less knowledge of SQL statements and are used to Brio, I wanted to know whether there is a “visual” non SQl statement writing way to apply “where clauses” only to the returned data in an outer join and not to an entire result-set.
Second question is about the Visualize Query option. This option seems to add a lot of “(” and “)” symbols in the statement, even to such account that the statement does not execute any more. I found this when an “IN (X,Y)”-clause in a Where clause got turned into “IN ((X,Y))” which blocked the execution of the SQL:
WHERE (CASE WHEN table.field IN (X,Y) THEN table.field2 = ‘A’ ELSE table.field2 = ‘B’)
turned into
WHERE (CASE WHEN table.field IN ((X,Y)) THEN table.field2 = ‘A’ ELSE table.field2 = ‘B’)
That looks like an error in Toad or you are adding () when Toad is about to do it for you. General rule is if you add a field from an outer join in the where clause you turn it into an inner join. You can make your outer join a complex join and put the condition from the where clause into the join (On table1.fieldA = table2.fieldA and table2.fieldB = ‘something’) this puts both the join condition and the test condition as part of the outer join, both condition must be true to pull table2 data but table1 data will always be there. In Query builder you can drag a second join condition between the two tables. Otherwise you have to change the where clause to table2.fieldB is null or table2.fieldB = ‘something’ so that you don’t change the outer join to an inner.
-So the adding “()” when not warranted seems to be a Toad bug or a limitation/consequence of the visualization algorithm. It seems to also copy the Joins into the “global Where clause” sometimes resulting in several copies of the join clauses when using several visualization steps.
-I found a way to do this by adding a “calculated” field with the ‘something’ to the ‘dominant table’ and then joining the appropriate field in the "outer’ table. It is still fairly convoluted and limits the type of limits you can put on the outer table or am I missing an easier way, I have been “right-clicking” everywhere in/on the joins but haven’t found another way to make the join "complex’ (yes I found the drop down to make the join < >,+<,<> etc.)
I realize this is an older thread - but our company is doing the same transition and I am having issues making the outer join, even with creating a calculated field.
This also does not work for me - I only get records that have never had a matching item in table 2.
Otherwise you have to change the where clause to table2.fieldB is null or table2.fieldB = ‘something’ so that you don’t change the outer join to an inner.
Any chance you were able to figure out another way?