Hi, here the explanation. - In case of a join resultrows are physically build. - The maximum length of physical rows in version 7.6 is 8088. - Because of your value (table1-column, table2-column) an intermediate resultrow would be build where both columns are in and the function can be used for.
As 4096 byte * 2 is longer than 8088, this intermediate resultrow causes the trouble although the final result would not exceed the maximum length. In newer 7.6-version than you use, a new join-handling is implemented, that these intermediate resultrows do not exist in a physically stored form --> your problem would not arise any more. Please check, if you can switch to the newest available version. Elke SAP Labs Berlin Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. > -----Original Message----- > From: Dusan Kolesar [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 24. Juli 2007 11:28 > To: MaxDB mailing list > Subject: [LIKELY JUNK]Row too long (-2000) > > Hello > I'm using MaxDb 7.6.0.34 on WinXp SP2. > > My DB schema is : > CREATE TABLE logTb > ( > "ID" Integer NOT NULL > DEFAULT SERIAL (1), > "TSTAMP" Timestamp NOT NULL > DEFAULT TIMESTAMP, > "TYPE" Integer NOT NULL DEFAULT 1, > "SENDER_ADDRESS" Varchar (255) ASCII NOT NULL DEFAULT '', > "RECEIVER_ADDRESS" Varchar (255) ASCII NOT NULL DEFAULT '', > PRIMARY KEY ("ID") > ) > // > CREATE TABLE inTb > ( > "ID" Integer NOT NULL DEFAULT > SERIAL (1), > "TSTAMP" Timestamp NOT NULL DEFAULT > TIMESTAMP, > "LOGTB_ID" Integer NOT NULL DEFAULT -1, > "DATA" Varchar (4096) BYTE NOT NULL, > PRIMARY KEY ("ID"), > FOREIGN KEY FK_LOGTB_ID ("LOGTB_ID") REFERENCES > logTb (Id) ON DELETE SET DEFAULT > ) > // > CREATE TABLE outTb > ( > "ID" Integer NOT NULL DEFAULT > SERIAL (1), > "TSTAMP" Timestamp NOT NULL DEFAULT > TIMESTAMP, > "LOGTB_ID" Integer NOT NULL DEFAULT -1, > "DATA" Varchar (4096) BYTE NOT NULL, > PRIMARY KEY ("ID"), > FOREIGN KEY FK_LOGTB_ID ("LOGTB_ID") REFERENCES > logTb (Id) ON DELETE SET DEFAULT > ) > > when I do: > > select > logTb.Id, > Value (inTb.Data, outTb.Data) as DataMsg > from logTb > left join inTb on logTb.Id = inTb.LogTb_Id > left join outTb on logTb.Id = outTb.LogTb_Id > > Syntax error or access violation;-2000 POS(8214) Row too long > > It seems that sql parser sums length of all columns listed in the > statement. > (Integer + Varchar (4096) BYTE + Varchar (4096) BYTE) > 8088 > bytes (max. > length of a table row) > But when I use Value (Col1, Col2), then maximal length of > this column is > max of Col1 and Col2 length. > > The same is when I do: > > select > logTb.Id, > Value (Substr (inTb.Data, 1, 10), Substr (outTb.Data, 1, > 10)) as DataMsg > from logTb > left join inTb on logTb.Id = inTb.LogTb_Id > left join outTb on logTb.Id = outTb.LogTb_Id > > Length of Substr (Column, 1, n) is n but not Length of Column type. > > Is there any way how to restrict length of output columns ??? > > > Thank you very much for support. > Regards, Dusan > > -- > Dusan Kolesar > Helsinska 19 > 040 13 Kosice > Slovakia > e-mail primary : [EMAIL PROTECTED] > e-mail alternative : [EMAIL PROTECTED] > ICQ# : 160507424 > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
