Union or intersect in FM 1

[JPCogAdmin]

Hi,

does anybone know how to do a union in framework manager?
Also, I've entered sql into a Framework manager using distinct and when I want to link the query subjects I don't see them correclty. In other words when using RS to pull individual items from the query subject I can't. I can only bring the whole thing. Whats up with this. Any body done and experienced this before.

-Jim

 

[mfgf]

Hi,

It depends whether you are using ReportNet or Cognos 8 Framework Manager. For Cognos 8, a new feature was added to allow you to define Query Sets (Union, Intersect or Except) between query subjects. For ReportNet, the only way to get anything similar was to code your own custom SQL in a query subject.

Regards,

MF.

 

[CognosProfessional]

Merge Query Subject may work better for you.

CP

 

[JPCogAdmin]

MF,

I'm working with Cognos 8. In Framework Manager 8 if I write my own nested query to create the query subject. Once I publish it to Cognos 8 ReportNet and use Report Studio to create reports, will I be able to select individual query items from the query subject or would the whole thing come into Report Studio?

CP,

If I merge the query subjects in Framework Manager what does the merge really do? In terms of the function being performed: Union, Intersect, etc.....

Thank .

 

[mfgf]

Hi,

If you write your own query SQL within your query subject, the items returned by the projection list of the query will be presented in Framework Manager, and if you decide to publish the query subject in a package, they will be available for use in a studio (individually or collectively).

If you choose to merge query subjects in FM, the result is a model query subject which does not contain its own hard-coded query. Instead, it uses the queries of the underlying query subjects you originally merged and the relationship defined between them to retrieve a result set. If the underlying queries have no relationship, you would then end up with a cross-product, which usually results in a governor error.

I guess what you are really looking for here is to have 2 query subjects with the same number of items (with the same data types) in each. You can select both query subjects, go to the Actions menu, and use the 'Define Query Set' option. This will take you through the process of defining a new query subject which uses either UNION, INTERSECT or EXCEPT on the underlying query subject results. You can then define appropriate relationships between this and any other query subjects, and publish into a package.

Hope that helps!

MF.

 

[JPCogAdmin]

MF,

That helps alot. Especially the portion where the union is created between the 2 query subjects. The portion where I was lost was when creating a manual query that uses inline views. For example, I used a query that had an inline view (a manually created sql) that had query items from both, the outside query and the inside query and when I was trying to use Report Studio to select individual items I couldn't. I don't know why but that's what was happening to me. Thank for your response MF.

-Jim

 

[mfgf]

Hi,

If you have hard-coded your SQL query to use inline views (or derived tables) your outer query (projection list) should include all items you want to use in the query subject later on. This must be the case in order to have a stable result set.

Is your query coded in Cognos, Native or Passthru SQL?

Regards,

MF.

 

[JPCogAdmin]

Hi MF,

I wrote the SQL as passthrough SQL. The query I had written had about 2 inline views and the outer query was listing columns as well as inline view columns. Thank you
for your response MF.