group by in a union

[axxo]

Is it possible to to a group by in a union query in Framework Manager? I can not do it. Usign determinants can do it but apparently not if the query is a union.

[MFGF]

Hi,

What database are you using?  What is the SQL syntax?  What error message do you get?

MF.

[axxo]

Thanks for the reply. I'm not at work atm, from memory the error msg goes something like "error in from clause". The data source sits on a MS SQL 2005 instance. I can post the syntax and error tomorrow. Although I have figured a way around the problem, it would be nice to know how to solve this one. Cheers

[MFGF]

Ok - send an example of what you are trying to do.  Also, have you coded it as Cognos SQL, Native SQL or Passthru SQL?

MF.

[axxo]

ERROR
This query contains an error and can not be executed.
It is recommended that you view the query feedback on the "Query Information" tab.
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 50: Syntax error near "from".

I haven't coded it - just dragged and dropped in Framework. Also I can't view the SQL in Query Info tab because of the error.

What have I done?
I have created a union query subject using define query set.
I create another query and drag in RegionName, RegionID from the union qry. Then add a new determinant to this qry and drag in those 2 columns to the key then check group by.
Try to run it and arrive at that error msg.

[MFGF]

Hi,

Your Query Set should be based on two existing query subjects which have the same number of query items, and whose query items are of the same data type and usage.

If you look at the two underlying query subjects and test them, do you get results back successfully?  Do they match as above?

Regards,

MF.

[axxo]

Hi,

The union works and the 2 queries that are used in the union also work and the data types are all the same, it's just when I try a group by using determinants that it falls apart.

[MFGF]

Hmmmm......

That doesn't sound right to me.  What determinants are you adding?

If they are all OK, I'd be very tempted to log this with IBM Cognos support - it may be you have found a "negative undocumented feature".

As a workaround, you could always copy the SQL from the two query subjects feeding into the Query Set and create a new data source query subject that used a UNION clause between the two queries.  This may help you get past the current issue?

MF.