I'd like to understand the best way to restrict items in a query. For example Query1 returns a list of ID's based of specific detail filters. I would like Query2 to return only items where Query2.ID is the same as Query1.ID.
Which Query2 Detail Filter is better? And why?
Note: Using an "=" filter requires Query2's "Cross product allowed" property to be set to "Allow".
IN is usually used to compare against a list, while = compares to a single value.
Examples:
... WHERE id IN (1, 2, 3)
vs.
... WHERE id = 1
Some documentation regarding Postgresql, which might be useful and similar to the cognos notation.
SQL efficiency - [=] vs [in] vs [like] vs [matches]
it depends on the underlying SQL engine. In MS-SQL, for example (according to the query planner output), IN clauses are converted to =, so there would be no difference
Which is better for you will depend on the specific report request. = is for single values and IN is for multiple values. It's all about the prompts. If you are hard-coding (so, not using a parameter) a filter that looks for a single value, use =.
But that's not really what you're asking...
Query2.ID = Query1.ID
You need a join, not a filter.
Using Cognos, consider an inner join as an option
In cognos, query explorer, from insertable objects/toolbox, drag JOIN below all of your queries (should create query 3)
Next, put Query 1 and Query 2 into the available openings
Click the join icon to the right of Query 3 Define cardinality from Query 1 to Query 2 as a 1.1 to 1.n (to achieve the filter effect). Click Okay
Next, single click on the join (just before Query 3 to get focus to set properties) and set the property for filter type to "In". Examine the generated SQL