FM Filters from two different databases

[BIengineer]

Hello!

I have a package containing tables from different Data Sources. These Data Sources are based on different databases, which in Oracle SQL cannot be queried on one another. Having the Query Subjects in the model separately works, and they retrieve data when testing just fine.

What I need to do however, is to filter out values in DB1.TableA.ColA from values in DB2.TableB.ColB

What I have tried so far, without luck:

1* Adding a "Where in" filter in the query subject, ie:
Select * From DB1.TableA
Where DB1.TableA.ColA not in (select ColB from DB2.TableB)

2* Adding a Query Subject containing "accepted values" - which returns what is expected when testing the query.
I then move the Query Subject down a level, and create a Filter object using an expression like:

DB1.TableA.ColA not in (DB2.TableB.ColB)

And then adding the filter to the corresponding table that needs to be filtered


Both of these approaches give me the UDA-SQL-0333 error, saying that it cannot find the given tables.

Is there some form of work-around that can be done here? Doing some DBA magic on the Oracle SQL side is not an option sadly, and it can't be implemented in Cognos itself either. It has to be made in FM.

Any ideas?

[bus_pass_man]

Isn't wonderful that the Cognos query engine has built into it functionality to plan queries which go against different data bases, which would be quite handy in cases such as yours?  The trick is to actually make use of it.

You are going about the process the wrong way.

The cognos best practices require that the query layer consist of query subjects formed by the expressions of the simple select * from {table} syntax. 

The filter should exist in the business layer.

There was a similar question asked here.
https://www.cognoise.com/index.php/topic,36881.0.html
and here.
https://stackoverflow.com/questions/64102239/cognos-11-filter-between-query-subjects

I asked you several question in the former and got answers to some of them and some of the answers actually helped but others made it less clear what you have set up in your model. 

Define 'without luck'.  What results did you get?  How was it wrong?

Are you doing action 2  as a data source qs?  Why?  Do that sort of thing in the business layer.  Filtering a query subject is not a particularly intellectually challenging task so I don't understand why you are running into problems even given the special hoops you want to try to leap through.

What does "move the Query Subject down a level" mean?

I'm assuming that table A and table B are part of a dimension (no not the thing with hierarchies, levels and members). 

[BIengineer]

Action 2 is a data source qs, but the filter is implemented in the next layer. All the query does is something like:

select * from table where ...

which works just fine when testing the qs.
When I move the qs down a layer, and try to create a filter using it - it fails, giving amongst other errors the UDA-SQL-0333 error.

The qs is moved from the query layer by creating a new qs in the next layer based on the model of the query layer qs - perhaps there is something wrong at this stage?

[BIengineer]

Allowing cross-joins in the model made the filters work now