Error with select statement

[Former Member]

Hello Everybody

The following sql command runs into an error(maxdb7.6):

(i removed the columns from the left joined tables from the select list to make it clearer)

select

substr(rbkontakt.rbkontakttext,1,254) as incident,

substr(rbkontakt.rbkontakttext,255,254) as incident2,

substr(rbkontakt.rbkontakttext,509,254) as incident3

from RBKONTAKT

join rbobjekte on rbkontakt.rbobjekteid = rbobjekte.rbobjekteid

left join stnlstamm on rbkontakt.stnlstammid = stnlstamm.stnlstammid

left join stauftragsstamm on rbkontakt.stauftragsstammid = stauftragsstamm.stauftragsstammid

left join stnlstamm as nlstamm on rbkontakt.stnlstammidabr = nlstamm.stnlstammid

left join rbkontaktmassnahmen on rbkontakt.rbkontaktid = rbkontaktmassnahmen.rbkontaktid

where rbkontakt.projekt = 'COBAMITTESUED'

-

---

Error -

---

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

Syntax error or access violation;-2000 POS(9027) Row too long

incident is varchar(1000)

The same command works against another table with an varchar(1000) column????

The same command works with join instead of left join ????

Any help welcomed

Best regards

Albert

[Former Member · Accepted solution]

Hi,

mhm, unfortunately in 7.6 the code is not optimized and the char (3000)-column is (intermediately) output in full length each time it is used, therefore causing an intermediate result of 9000 char.

(Ok, noone said that the developers are proud of it. They changed it for newer releases.)

To have a workaround you could do

select

substr(rbkt,1,254) as incident,

substr(rbkt,255,254) as incident2,

substr(rbkt,509,254) as incident3

from ( select rbkontakt.rbkontakttext rbkt

from RBKONTAKT

join rbobjekte on rbkontakt.rbobjekteid = rbobjekte.rbobjekteid

left join stnlstamm on rbkontakt.stnlstammid = stnlstamm.stnlstammid

left join stauftragsstamm on rbkontakt.stauftragsstammid = stauftragsstamm.stauftragsstammid

left join stnlstamm as nlstamm on rbkontakt.stnlstammidabr = nlstamm.stnlstammid

left join rbkontaktmassnahmen on rbkontakt.rbkontaktid = rbkontaktmassnahmen.rbkontaktid

where rbkontakt.projekt = 'COBAMITTESUED' ) x

This should do. Unfortunately an intermediate result is prepared in which the kontaktext is stored as one column and then from this intermediate result the final result with the substr-pieces is prepared. This will of course need a little bit more time and space(temporarily). But if cutting is necessary and the kernel-version is fixed, then I do not see another chance.

Good luck,

Elke

[Former Member]

Hi,

incident cannot be varchar (1000) as we see it as result of a substr having the length of 254. Do you mean rbkontakttext to be varchar (1000) ?

You did not mention if varchar (...) ASCII or VARCHAR (...) UNICODE is used. This may be THE difference between a statement working and another one not working.

If you would NOT substr the same column 3 (or 4) times, it could help. Which reason do you have to cut rbkontakttext in pieces?

You did not show us the table definitions for all the tables concerned and you did not show the full select-list. But these (not shown) select-list-columns may cause the (intermediate) resultset to exceed the maximum row length.

Please provide the table definitions (incl. the specification of codetype UNICODE/ASCII), the full select and the result of an explain for the working select with (normal) join and the explain for the not-working select with left join.

Then perhaps we can provide some further ideas/hints.

Elke

[Former Member]

Hello Elke

Thank you for your quick answer.

Sorry !! I'm not heat-proofed

The column definition:

"RBKONTAKTTEXT" Varchar (3000) ASCII NOT NULL DEFAULT ' '

This one works:

select

rbkontakt.rbkontakttexT as incident

from RBKONTAKT

join rbobjekte on rbkontakt.rbobjekteid = rbobjekte.rbobjekteid

left join stnlstamm on rbkontakt.stnlstammid = stnlstamm.stnlstammid

left join stauftragsstamm on rbkontakt.stauftragsstammid = stauftragsstamm.stauftragsstammid

left join stnlstamm as nlstamm on rbkontakt.stnlstammidabr = nlstamm.stnlstammid

left join rbkontaktmassnahmen on rbkontakt.rbkontaktid = rbkontaktmassnahmen.rbkontaktid

where rbkontakt.projekt = 'COBAMITTESUED'

resultset with 4570 rows

this one not:

select

substr(rbkontakt.rbkontakttext,1,254) as incident,

substr(rbkontakt.rbkontakttext,255,254) as incident2,

substr(rbkontakt.rbkontakttext,509,254) as incident3

from RBKONTAKT

join rbobjekte on rbkontakt.rbobjekteid = rbobjekte.rbobjekteid

left join stnlstamm on rbkontakt.stnlstammid = stnlstamm.stnlstammid

left join stauftragsstamm on rbkontakt.stauftragsstammid = stauftragsstamm.stauftragsstammid

left join stnlstamm as nlstamm on rbkontakt.stnlstammidabr = nlstamm.stnlstammid

left join rbkontaktmassnahmen on rbkontakt.rbkontaktid = rbkontaktmassnahmen.rbkontaktid

where rbkontakt.projekt = 'COBAMITTESUED'

The explain of the second command runs into the same error -2000 row to long.

??????

We tried to splitt the column because of the behavior of our application.

It doesn't show variables longer then 256 signs in special grids.

Nevertheless i can't understand, that the second command shows an error.

From my understanding the result row must be shorter (3x254) as the working result of the first command (1x3000)

Best regards

Albert