Columns of type BLOB are not permitted in a GROUP BY or ORDER BY clause.

[Cosmic_Jester]

Hi all, I am setting up a unioned query. I have both parts working in SSMS. I have built my individual SQL queries ( FM is not an option for this report at present sadly) and the first query runs fine.
The second query gives the error:
Columns of type BLOB are not permitted in a GROUP BY or ORDER BY clause.
I have no group by or order by in my SQL and as far as I know I have no blob data types unless Cognos is converting a data type at run time.
Has anyone experienced this before and if so how did you fix it?

Many thanks,
Dave

[the6campbells]

One or more of the columns projected by your union is being described your database client (driver) such that it being bound as one of CLOB, NCLOB etc.
Some vendors drivers may describe a column with a data type/precision which effectively makes it appear to be a large character type.
Are you returning NVARCHAR MAX, XML, .... ?

A set operation (UNION, INTERSECT and EXCEPT) by default remove duplicates (i.e. implicit DISTINCT) unless the ALL property is set.

Hence, for some systems that can cause those operations to faill.


BI-Queries in CA are implicitly AUTO-GROUP and SUMMARIZE which means that the query will group on the columns of type identifier.
In other words the query may generate a DISTINCT/GROUP BY.

If AUTO-GROUP query property is false you get a detail (non-grouped) query.