SELECT COUNT(*) with subselect not pushed down
When issuing a SELECT(*)....that contains a subselect on MS SQL Server or DB2/i nickname, why isn't the query pushed down to datasource?
An example query is:
select count(*) from
( select int1, SUM(COALESCE(int2, 0)) AS sum2, SUM(COALESCE(int3, 0)) AS sum3
from nktab1 group by int1, int2 ) A
The above query is rewritten to bring back the data and evaluate the select at the Federation Server because the GROUP BY is blocked for being pushed down. To resolve, set the server option DB2_GB_MORE_THAN_NON_AGGR to 'Y' and retry.
You can issue the following statement:
ALTER SERVER <server_name> OPTIONS( ADD DB2_GB_MORE_THAN_NON_AGGR 'Y').
More support for:
InfoSphere Federation Server
Data Sources and Wrappers - Microsoft SQL Server
Software version: 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: All Editions
Reference #: 1443146
Modified date: 04 December 2013