IBM Support

SELECT COUNT(*) with subselect not pushed down

Technote (FAQ)


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:


Document information

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