SQL0840N Too many items were returned in a SELECT list

[rajurokz]

Hello,

I'm getting the below error when trying to create a relationship between a master table and a dimension. Master table has more than 300 columns and the dimension has 200+ columns, please help me get rid of the below error.

BMT-MD-0003 UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][CLI Driver][DB2/LINUXX8664] SQL0840N Too many items were returned in a SELECT list. SQLSTATE=54004


RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][CLI Driver][DB2/LINUXX8664] SQL0840N Too many items were returned in a SELECT list. SQLSTATE=54004

thanks,
rajurokz

[Lynn]

Hello,

I'm getting the below error when trying to create a relationship between a master table and a dimension. Master table has more than 300 columns and the dimension has 200+ columns, please help me get rid of the below error.

BMT-MD-0003 UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][CLI Driver][DB2/LINUXX8664] SQL0840N Too many items were returned in a SELECT list. SQLSTATE=54004


RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][CLI Driver][DB2/LINUXX8664] SQL0840N Too many items were returned in a SELECT list. SQLSTATE=54004

thanks,
rajurokz

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/n840.html

[rajurokz]

is there any other work around other than splitting the Query subject? I have around 49 tables with 1000+ columns in each, its a hurdle. Please suggest me the best approach to handle this.

thanks,
rajurokz

[bdbits]

Sounds to me like your data model is ... not trying to be mean here ... pretty badly broken. You would have a hard time convincing me of a good reason for that many columns, especially in a data warehouse (which terms like dimension table suggest you have here).

My guess is that you either have things like "Jan Value", "Feb Value", "Mar Value", etc. or you have things together in a single table that belong in multiple tables. If you have no control on the source database to get this fixed, perhaps you could use FM query subjects to split things out properly, much like using views on a database. It will not perform as well as getting a better database design, but at least it would work.

[the6campbells]

Consider that DB2 is imposing the constraint.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00840n.html

But, you may also want to review what you are doing in your models to cause queries to project such a large # of columns etc.