Author Topic: SQL0840N Too many items were returned in a SELECT list  (Read 525 times)

Offline rajurokz

  • Full Member
  • ***
  • Join Date: Mar 2016
  • Posts: 11
  • Forum Citizenship: +0/-0
SQL0840N Too many items were returned in a SELECT list
« on: 12 Oct 2017 04:06:39 pm »
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

Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,601
  • Forum Citizenship: +336/-1
Re: SQL0840N Too many items were returned in a SELECT list
« Reply #1 on: 13 Oct 2017 02:11:43 am »
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

Offline rajurokz

  • Full Member
  • ***
  • Join Date: Mar 2016
  • Posts: 11
  • Forum Citizenship: +0/-0
Re: SQL0840N Too many items were returned in a SELECT list
« Reply #2 on: 13 Oct 2017 02:58:27 pm »
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

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,799
  • Forum Citizenship: +105/-0
Re: SQL0840N Too many items were returned in a SELECT list
« Reply #3 on: 24 Oct 2017 05:41:16 pm »
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.

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 88
  • Forum Citizenship: +6/-0
Re: SQL0840N Too many items were returned in a SELECT list
« Reply #4 on: 24 Oct 2017 06:39:30 pm »
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.


 


       
Twittear