SQL0101 - SQL statement too long or complex

[DeadManWalks]

Hi , I ran an sql and got this error

Message . . . . :   SQL statement too long or complex.                         
Cause . . . . . :   The SQL statement is longer than the limit allowed for     
  length or complexity.  The reason code is 2.  One of the following reason    
  codes indicates the error:                                                   
    1 - The total number of subselects in a fullselect (UNION or UNION ALL     
  clause) is greater than 32.                                                  
    2 - The total number of columns, constants, and operators is greater than  
  the SQL limits. 

The SQL

SELECT t.ACVEND, t.ACITEM, t.ACLOCN, t.ACeDAT, MIN(t.ACSEQN) as     
MinSeqN , MAX(t.ACSEQN) as MaxSeqN , COUNT(t.ACSEQN) as CntSeqn ,   
(select acoavg from mm4r4lib/invcst b where b.acseqn = min(t.acseqn)
 ) as Begaco, (select acnavg from mm4r4lib/invcst e where e.acseqn =
 max(t.acseqn) ) as EndAco FROM MM4R4LIB/INVCST t where t.acidat >= 
 1060913 GROUP BY acitem, aclocn,acvend,acedat ORDER by acitem, aclocn,
 acvend, acedat 

if i remove one of the nested selects it will work fine.

Any one have a similar problem?

[kpmac]

Re: SQL0101 - SQL statement too long or complex

In words what are you trying to do?

I dont think you can use the max function in the WHERE clause. However, you can use max in the HAVING clause.

Try running your subselect as a standalone query and insert a dummy variable into the max.

[DeadManWalks]

Re: SQL0101 - SQL statement too long or complex

If I remove either of the following from the SQL it works;

(select acoavg from mm4r4lib/invcst b where b.acseqn = min(t.acseqn)) as Begaco 

or

(select acnavg from mm4r4lib/invcst e where e.acseqn = max(t.acseqn)) as EndAco 

This is what the data looks like with the removal of the second select;

VENDOR NO  ITEM NO          LOCATION  ENTRY DATE       MINSEQN       MAXSEQN         CNTSEQN           BEGACO 
 489,037   05109Y               812    1,060,914       538,506       538,583               4          99.5400 
 489,037   05109Y               812    1,060,921       549,295       549,296               2          99.5804 
 489,037   05109Y               875    1,060,924       554,529       554,530               2          99.6598 
 489,037   05109Y               878    1,060,923       552,569       552,570               2          99.6294

This is what the data looks like with the removal of the first select;

VENDOR NO  ITEM NO          LOCATION  ENTRY DATE       MINSEQN       MAXSEQN         CNTSEQN           ENDACO 
 489,037   05109Y               812    1,060,914       538,506       538,583               4          99.5946 
 489,037   05109Y               812    1,060,921       549,295       549,296               2          99.5972 
 489,037   05109Y               875    1,060,924       554,529       554,530               2          99.6892 
 489,037   05109Y               878    1,060,923       552,569       552,570               2          99.6598 
 489,037   05109Y               882    1,060,922       550,476       550,477               2          99.6294

Notice the "BegAco" and the "EndAco". I just want these 2 computed fields in one select.

[arrow483]

Re: SQL0101 - SQL statement too long or complex

You can also get this error if you have too many functions over null values.

For example if your MAX(t.ACSEQN) as MaxSeqN has records where t.ACSEQN has some null values, you will get an error when the number of null records exceeds some "magic" number.

I solved it in my case by adding a COALESCE inside the max. Some thing like: MAX(Coalesce(t.ACSEQN,0)) as MaxSeqN

Before you crazy trying to handle the too complex thing, see if this type of error could be the problem.

[DeadManWalks]

Re: SQL0101 - SQL statement too long or complex

Changing/Inserting the MAX(Coalesce(t.ACSEQN,0)) as MaxSeqN into the statement still yielded the same SQL error as stated earlier.
Thanks, we have broken the statement into 2 parts to get around this.
Thanks for the effort.

[pjk]

Re: SQL0101 - SQL statement too long or complex

I'm not sure if this is the same error I've had before, but... there is a maximum limit on the data that can be returned by a single SQL fetch. IIRC it's like 32K for DB2 anyway. If you exceed that, you might get this error--if the total characters exceeds 32k in a single fetch row.