"IN" clause for filter, but between Queries going to different DB/Schema's

[tsamost]

Hi,

I have a report written by one of our users that has mutliple queries, one of which has a filter where it uses an "IN" clause with the results of another query, IE:

MAIN_QUERY.ASSET_NUMBER in (SUB_QUERY.ASSET_NUMBER).

Recently, our development group decided to move certain custom tables to a different DB/Schema, so the table referenced in SUB_QUERY is not on the same DB as the table referenced by MAIN_QUERY.

Now, when we run this report, we get an error saying the table from SUB_QUERY does not exist.

If we use the data found in SUB_QUERY as its own standalone report, it runs fine.  But when used in the filter for MAIN_QUERY, the error results.

Can Cognos (8.4.1) not handle the IN clause if it points to a table in another DB?  I have Limited Local set on all the datasources, so I would have thought it just would have brought that functioning to the Cognos server, rather than at the DB server.

Any insight appreciated.....

Thanks,

Todd - American Tower Corp.

[navissar]

Hey there, Todd,
Some things I would look into:
1. Has the underlying model been changed to accomodate the changes to the schema? If a table has been relocated to a new place, the FM model should be changed accordingly to include this new location as a datasource and to bring this data along. Easiest way to test this is to run SUB_QUERY (Via the View tabular data functionality) from the very same report that's giving the error message. If it runs, it means that the model knows where to look for the data. If it doesn't, it means that the model probably needs to be fixed. You wrote that you can use the data from SUB_QUERY on a standalone report, but did not mention whether it was from the same model.
2. You want to compare an item to a cached item. For this, Verify that the queries run sequentially and not together, and that SUB_QUERY runs first and is cached (These are all query properties).
3. What is the error message thrown? Is there a log entry? Sometimes the error message/log entries can educate as to the exact source of the issue.
4. If all else fails, try converting SUB_QUERY to SQL and see if that works.

Looking forward to hear the solution,
Rod - LiBi Software Technology

[tsamost]

Rod,

Toda Raba! :-)  Sorry for my lack of explanation; everything is from the same model, and the Tabular Data for the SUB_QUERY returns OK.  It's only failing when the MAIN_QUERY, going against another DB/Schema table, uses the SUB_QUERY in the filter via the IN clause.....

The error we see is:

"UDA-SQL-0107 A general exception has occurred during the operation "columns".ORA-00942: table or view does not exist UDA-SQL-0327 An invalid object name was detected in the SQL request.UDA-SQL-0333 The table "XXGAM.ATC_DW_GAM_ASSET_V" was not found in the dictionary"  [the rest cut out, not relevant]

This view, XXGAM.ATC_DW_GAM_ASSET_V, is the one referenced by SUB_QUERY.  The MAIN_QUERY goes against a view called XXATC.ATC_DW_TENANT_LEASE_HEADER_V (different DB & Schema).

Again, if I use the XXGAM.ATC_DW_GAM_ASSET_V in a standalone report/query, or view the Tabular Data from the SUB_QUERY, all is fine.  I'm thinking that Cognos isn't handling the IN clause properly in SQL, and it's trying to generate the sub-select in SQL that can't be done across 2 data sources.....just looking for either confirmation on that, or a way around it via settings in the report or query.

I'm working with the user to try and handle it via Query Joins inside Report Studio instead, which will ensure the processing is done in Cognos; but it also slows the report down.

Thanks again for the reply,

Todd.

PS - how long have you been at Libi?  I used to work with someone who was there for a while, Yossi Hanein....

[tsamost]

By the way, will try to change the Sequence and Caching, thanks for that pointer too!

[navissar]

Hey Todd,
I got to LiBi a bit over a year ago. Didn't get to work with Yossi.

Here's another thought triggered by another post: Run the original query without the filter, then make a reference query for that query which has the filter:
Main_Query_With_filter<-Unfiltered_Original_Query
this will force Cognos to first run the (unfiltered) query, then apply the filter over the result set (Thus not combining the filter and the main query in the same statement).

[tsamost]

Thanks again, will try that.  The Sequential processing and caching did not do the trick......