left outer join in data view

[ciber]

Hi all,

I try to use an left join in my FM data view namespace like this:

select
[table_1].[field-A] ,
[table_1].[field-C] ,
[table_2].[field-B]
from
    [table_1] as [table_1]
left outer join
    [table_2] as [table_2]
on [table_1].[ID] = [table_2].[ID]
   
When I test the table object in FM I doesn't get any data from [field-B] in Cognos.
When I go to the Query Information and copy the Native SQL to my SQL Server and run the same statement I do get data from [field-B]


Why do I get no data in Cognos FM and do get data in the database?

Please help!

[blom0344]

FM performs a test on a limited set by default returning just a subset of data. This may give the impression that no data is returned from the second table. You can overrule the testsetting in FM..

[ciber]

Hi Blom0344,

I know about the functionality.
But when I retrieve all the records, I still doen't get any data.
So it goes wrong in FM, not the SQL.

CIBER

[blom0344]

The native SQL that FM displays can be easily tested by having a trace running on the database. That is the best way of confirming that the statement is executed as it should be.
Secondly, FM uses a datasource connection underneath. There may be a difference between rights between the datasource logon and the account you use directly on the DB

[tupac_rd]

Also, check for Governor settings if you have "Allow" for Outer Joins.

[blom0344]

tupac,

Good one   ;)

[ciber]

Hi,

I don't have rights to analyse the SQL trace on the server(s). So I can't look at the SQL Server trace.

I have created a VMware on my laptop with the same data and the same Framework model.
The SQL server on this VMware is 2008, and i installed Cognos 8.4 32bits.

(The work situation is: database: SQL 2005 (database tools 2008 ( ??? ) Cognos 8.4 64bits)

When I test the outer join on my laptop, its working fine!
I have to analyse more (is it the 64 vs 32 bits? is it SQL2005 vs SQL2008?).

Outer joins are in both situations allowed.

CIBER

[ciber]

Right,

How to spent your time .......

I don't know exact the solution, but some guys have installed SQL server fixes on the SQL Server machine.
When I test my Framework, I get the data as expected.

So, I was looking in the wrong direction I think.  >:(

But it works now  ;D