Sub query in filter but not in calculated field

[cmm1863]

Say i have two queries; Query1 and Query2.

I believe I am able to use Query1 as a "subquery" in the filter for Query2. For example the filter would be
   (select...from...where) [ID] in ([Query1.ID])

I am not able to use it in a calculated field though. For example i cant create a field that is
   case when [ID] in ([Query1.ID]) then 1 else 0 end

Is this true? If it is true why is that the case?

(I can work around most issues like this by joining queries but "subquerying" is often my first instinct and easiest line of thought.)

Thanks,
Chris

[bdbits]

Filters have to evaluate to a "meets this condition", true or false.

Put the case statement as a calculated data item in query1, then in query2 your filter would be something like "[yourcalc] = 1".

[cmm1863]

Right, to be clear i wasn't really saying my two examples do the same thing, just that one is possible and one isn't.

I am basically creating a field for if a person returned or not. Query1 was the first instance, Query2 was the second (if they returned they would be in Query2). So my first instinct was to subquery and use a case statement, but that didn't work. I know i can accomplish this by left joining the second query and if they weren't in Query2 it would be null (i would probably add a constant field of 1 to query2 and select that).

My real question is why cant i subquery in the case statement but i can in the filter? My life would be a lot easier if i could.

Thanks,
Chris

[cmm1863]

Also, thanks for the reply and sorry if this is a commonly talked about limitation. Im new to cognos.

[bdbits]

Because a filter *must* evaluate to a boolean. Your expression

    case when [ID] in ([Query1.ID]) then 1 else 0 end

does not result in a true or false, it returns an integer with a value of 1 or 0. While you can sometimes replace true/false with 1/0 I do not think that is true in a detail/summary filter.

[cmm1863]

Because a filter *must* evaluate to a boolean. Your expression

    case when [ID] in ([Query1.ID]) then 1 else 0 end

does not result in a true or false, it returns an integer with a value of 1 or 0. While you can sometimes replace true/false with 1/0 I do not think that is true in a detail/summary filter.

Thanks, i did a really bad job asking this.

Ignore the filtering.

If I'm trying to create a calculated field that returns a 1 or 0 and do that case statement referencing the other query it doesn't work. I don't get an error it just never completes running.

I only mentioned the filter because that worked, while my calculated field didn't.

Thanks,
Chris

[Lynn]

If you look at the generated SQL for the approach you are attempting it might give you some clues about what is going on. I suspect that Cognos is bringing back two separate result sets and then attempting to put the story together locally on the Cognos server rather than executing everything directly in the database.

The general approach for comparing result sets would be to use a join in the query explorer, although I can't say for sure that your sub-query approach isn't valid. Of course a join in the report would also bring back both result sets and put them together on the Cognos server which is always the least desirable thing unless the two result sets are very small. Since you are looking at something called [ID] I suspect there could be two rather large result sets.

Whenever gymnastics, such as extremely complex expression and/or multiple queries, are required in report authoring it may be a red flag indicating that the model isn't really constructed following a star schema approach with the necessary dimensions to answer the necessary business questions. I'm not sure if you have any options with regard to the Framework Manager model but thought I'd mention it in case there is an option to address a potential problem at the source.

Sorry I'm not really answering your question but perhaps some thoughts to direct your further investigation.

[cmm1863]

Thanks for the reply. That's a good idea, ill look at the generated sql and see what its doing.

Unfortunately these sort of subqueries are probably going to be routine for me as our reporting needs are all over the place. I know i can create a new query and join it, i was just really hoping i wouldn't have to each time.

[cmm1863]

If you look at the generated SQL for the approach you are attempting it might give you some clues about what is going on. I suspect that Cognos is bringing back two separate result sets and then attempting to put the story together locally on the Cognos server rather than executing everything directly in the database.

The general approach for comparing result sets would be to use a join in the query explorer, although I can't say for sure that your sub-query approach isn't valid. Of course a join in the report would also bring back both result sets and put them together on the Cognos server which is always the least desirable thing unless the two result sets are very small. Since you are looking at something called [ID] I suspect there could be two rather large result sets.

Whenever gymnastics, such as extremely complex expression and/or multiple queries, are required in report authoring it may be a red flag indicating that the model isn't really constructed following a star schema approach with the necessary dimensions to answer the necessary business questions. I'm not sure if you have any options with regard to the Framework Manager model but thought I'd mention it in case there is an option to address a potential problem at the source.

Sorry I'm not really answering your question but perhaps some thoughts to direct your further investigation.

Thank you again. I'm new to cognos and have never looked at the generated sql before. It definitely gives an idea of whats going on behind the hand waiving. Cognos was selecting a set to use later and aliasing it, then "re-aliasing" the same same set for the other queries later on. I was able to turn of the "use sql with clause" and the sql is much more straight forward and the results are pretty much instant. Now i just have to be weary as to situations beneficial to use/not use the with option.