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!
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..
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
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
Also, check for Governor settings if you have "Allow" for Outer Joins.
tupac,
Good one ;)
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
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