outer join HELP!

[Guest_imported]

Hi I compare two table. In my catalog i put my join example:

t1.seq = t2.seq.

And I put a check in Outer Join Box because I would like to see all transaction of t1 even if t2 have not.

I would like to see:

all transaction matching between t1 and t2 AND
all transaction t1 in the same report. BUT Even if put LEFT OUTER JOIN check box, In my report only matching transaction in the two tables appears.....

HELP ME

Mart

 

[griffindm]

Two points,

The checkbox does not indicate LEFT OUTER JOIN. It is merely a graphic indicating which side will include rows with missing values on the other side. You can rearrange the tables (side-to-side) and the graphic changes to continue to show this relationship.

Secondly, be careful that your filter statement does not overrule your outer join. If you have filtered on a t2 value, you must modify the statement to say:

(original t2 filter OR t2.colname is missing)) ...

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[Guest_imported]

Even If i put in the join (t1.seq = t2.seq OR t2.seq is missing) my transaction (who didn't matching with t2) didn't appears in my report....

Can you explain me step by step how i can do this.


Thank you

 

[griffindm]

Just to confirm: The Catalog | Joins screen shows the checkbox on the T1 side of the relationship.

You should also look at the SQL generated on the Report | Query |Profile tab under the SQL button. It would help if you posted that SQL code here.

Thanks,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[griffindm]

Marti26,

Please join the forum. Don't start new threads to reference this one as it makes additional work for thoses users looking to help you.

Thanks,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[martine]

this is my condition in my catalog:

(LEFT)T1 have a check in outer join box and not in t2 and i have

arrow and = and arrow between t1.seq and t2.seq

i put OK

In my report i have a prompt between date and date

and t1.seq <> 0 and t2.seq<> 0


thank martine

 

[griffindm]

martine,

Change the filter from and t1.seq <> 0 and t2.seq<> 0
to

and t1.seq <> 0 and (t2.seq<> 0 OR t2.seq is missing) ...

Let me know if this fixes it.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[martine]

select T2.matl-trans-num, T2.acct, T1.trans-num, T1.trans-date, T1.acct, T1.dom-amount, T1.ref, T1.matl-trans-num, T1.for-amount, T2.trans-num, T2.trans-date, T2.dom-amount, T2.ref, T2.for-amount
from {oj mcan.ledger T1 LEFT OUTER JOIN mcan.ledger T2 on T1.matl-trans-num = T2.matl-trans-num }
where T1.trans-date >= {d '2002-07-08'} and T1.trans-date <= {d '2002-07-08'} and T1.matl-trans-num <> 0 and T1.acct = '220310'

 

[griffindm]

martine,

I see no reference to T2 in the filter, which is good, so there are no conflicts there with the outer join, BUT ...

Impromptu often does not pass ALL of the filter conditions to the SQL (based on local processing, local functions, or complex outer join requirements).

Try turning the filter OFF (use the suppress filter checkbox) and see if outerjoin values (T1 without associated T2 values) are appearing in the report.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[martine]

nothing happens...

i have only transaction match

 

[griffindm]

martine,

If you have access to a SQL interpreter (SPL-Plus[Oracle]), ISQLW[MS], or SQL-Talk[Centura,etc]) can you copy and paste the code there after connecting to the database with the same ID as Impromptu uses? If so, do you only see the inner-join results there also?

Dave G.
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[martine]

i past by ODBC...

 

[griffindm]

martine,

Using ODBC as your connection, the best way to test SQL is probably via MS Access, but it's a little involved and I don't have time to go through it here. If you know how, try it there and get back with the results. Otherwise I'm at a bit of a dead end. You may want to try Cognos support assuming you are signed up for ongoing support (OSP).

Dave G.
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[martine]

it is possible that my table will be two alias. It is possible outer join function not correct with alias???

 

[griffindm]

Outer-joins are supported with aliases in Impromptu. That should not be a problem, unless it is a self-join (aliased to itself). Even then, I think is should still work.

HTH,

Dave G.
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[nagrajm]

I was studying the above SQL with interest.

&quot;mcan.ledger&quot; is self joined on &quot;matl.trans.num&quot;. Now that is odd.

Generally, if a self join invloves join between different columns, an outer join would make sense and one can expect to see rows that don't have a match.

In the above instance, the join columns are one and the same(matl.trans.num). Logically, every row in T1 would have a matching row in T2 and vice versa.

I think martine should re-evaluate the report design. I am unable to throw any more light on this except to mull over the paradox of self join involving only one column.

 

[griffindm]

Good Point. I was concentrating on the filter clause more than the from. If the T2 table alias has no filter conditions to eliminate rows, you would certain wind up with an apparent inner-join in a self-join.

Sounds like a design problem.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[nagrajm]

I will follow-up my earlier post with an example.

Say, you have the classic EMPLOYEE table. You add an alias called EMPLOYEE_ALIAS to the catalog tables and join them as

EMPLOYEE.employee_no = EMPLOYEE_ALIAS.employee_no (+)

I cannot visualize any business requirement where the above join would make a logical sense.

However,

EMPLOYEE.employee_no = EMPLOYEE_ALIAS.employee_supervisor_no (+)

would help fetch the details of all supervisors and their subordinates. The outer join here would also fetch all employees that do not have any subordinates.

Without the outer join only employees who have one or more subordinates would be fetched.

Therefore, I would think that martine has not related T1 and T2 in the catalog that would make business sense for the report he is trying to retrieve.

Probably matl.trans.num should be joined to trans.num. I am just guessing here but I am sure you can understand what I am driving at. Without any knowledge about the business process I cannot venture any more guesses.

 

[martine]

OK I explain,

with a example

T1 is an alias of ledger with account 420 AND
T2 is an alias of ledger with account 421

matl.tran.num is a sequence for retrieve all transactions in the 2 accounts

I would like compare this 2 ledger accounts BUT

I would to see all transactions in account 420 even if is not in account 421.....

If my design is not correct what do you propose me?

Thank you so much..

Martine

 

[nagrajm]

Had a few question based on your inputs.

1) I do not see additional filter conditions in the SQL in effect saying (T1 ledger account = 420 and T2 ledger account = 421). Has this condition been set as a part of the catalog join definition?

2) Are you referring to the column T1.acct when you say &quot;ledger account&quot;. If so, then the filter condition &quot;T1.acct = 220310&quot; needs further explaining.

3) Remove the filter condition &quot;T1.matl-trans-num <> 0&quot; and run the report to see if that condition in any way interferes with the outer-join.