FM Filter Query

[redmist]

I have a sql statement that I'm trying to duplicate in FM.

select
XXXXXXX
from
FACT a,
TABLE b,
TABLE c,
TABLE d
where a.a1 = c.a1  and
      a.a2 = d.a2
      and a.a3 = b.a3
      and a.a4 = b.a4
      and a.a4 in ('a','aa')
      and b.b5 = 'as'
      and a.a6 <> 'z'

I ran the query in toad against oracle and this returned 9000 rows
-------

I then create the model in FM by importing the 4 tables and creating the joins in the Database layer and applied all the filters.
I then created a model QS in different namespace (Business layer) and pulled the required columns from the tables in the Database layer.

When I did a row count on the Model QS, i got 23000 rows instead of 9000 rows.
--------------

I finally got the correct record count by applying the filter (b.b5 = 'as') against the Model QS instead of the QS in the Database layer.

Any ideas as to why it does not work when applying filter against the table
Is there something basic I'm missing?

Thanks
J

[david.stachon]

you're best way to troubleshoot is to see what SQL cognos is generating.

On your "business layer" subject, open it, go the the "Test" tab, hit "Test Sample"

...then, go to the "Query Information" tab to see what's going on.

[blom0344]

I would suggest taking up ANSI style for the joins. Oracle supports this from version 9 (?) or so upwards. You will probably notice that Cognos uses ANSI style as well.

In your case the SQL would be:

Code Select Expand


select
XXXXXXX
from
FACT a inner join
TABLE b on a.a3 = b.a3 and a.a4 = b.a4
inner join TABLE c on a.a1 = c.a1
inner join TABLE d on a.a2 = d.a2
where  a.a4 in ('a','aa')
      and b.b5 = 'as'
      and a.a6 <> 'z'