Fabio Pinotti wrote: > > The column C_TREE_PATH is used inside many tables, and many queries in the > application are sorted by this field. > Because ORDER BY accepts a max length of 1000 chars, I used so far a > rtrim(substr(c_tree_path,1,1000). But I think it's not useful to have a > length of 4000 when I can use only 1000. > So I decided to redefine the column length to 1000. This value should be > enough. > Now it works.
Sounds good. > > I have just another question: you said that "The definition length is > checked, not the length of actual stored values". This may be the cause of > "row too long" errors I get on some queries, with SELECT parts of sql > statement. So, it is useless the SUBSTR in those cases, isn't it? > No, I would not say it is ALWAYS useless. Usually the substr will be done early enough to shorten the output. Only in cases where the kernel has to prepare such internally temporary aux_file with some result of a join in before the kernel can do the final select with the predicate/expression including columns of two different tables (your like or tab1.col1 + tab4.col5 for example), then it is useless. Elke SAP Labs Berlin > Thanks a lot! > > Fabio > > > > ----- Original Message ----- > From: "Zabach, Elke" <[EMAIL PROTECTED]> > To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Thursday, July 15, 2004 2:35 PM > Subject: AW: ERROR: Row too long > > > > Fabio Pinotti wrote: > > > > > > Hi, > > > I have the "Row too long" error with the following query: > > > > > > SELECT > > > b.K_SUBJECT_TYPE, > > > b.N_SUBJECT_TYPE, > > > to_char(GET_HRC_LEVEL(rtrim(substr(b.C_TREE_PATH,1,1000)),null)-1) > > > FROM > > > (SELECT rtrim(substr(C_TREE_PATH,1,1000)) C_TREE_PATH > > > FROM SUBJECT_TYPE > > > WHERE > > > NVL(D_END,TRUNC(SYSDATE+1))>TRUNC(SYSDATE) AND > > > D_START <= TRUNC(SYSDATE) AND > > > K_SUBJECT_TYPE IN ( > > > SELECT TO_NUMBER(SUBJECT_ANCESTOR(K_SUBJECT_TYPE)) MYKEY > > > FROM SUBJECT_TYPE > > > WHERE NVL(D_END,TRUNC(SYSDATE+1))>TRUNC(SYSDATE) AND > > > D_START <= TRUNC(SYSDATE) AND > > > N_SUBJECT_TYPE LIKE 'Alt'||'%')) A > > > INNER JOIN SUBJECT_TYPE B ON (B.C_TREE_PATH LIKE A.C_TREE_PATH ||'%') > > > order by > > > rtrim(substr(b.c_tree_path,1,1000)) > > > > > > The table defintion is: > > > > > > CREATE TABLE SUBJECT_TYPE ( > > > K_SUBJECT_TYPE NUMBER (6) NOT NULL, > > > N_SUBJECT_TYPE VARCHAR2 (50) NOT NULL, > > > D_START DATE NOT NULL, > > > O_MODIFY DATE NOT NULL, > > > E_OPERATOR_K_OPERATOR VARCHAR2 (20) NOT NULL, > > > N_SHORT_SUBJECT_TYPE VARCHAR2 (20), > > > K_PROG NUMBER (3), > > > C_NOTE VARCHAR2 (250), > > > D_END DATE, > > > C_TREE_PATH VARCHAR2 (4000), > > > E_SUBJ_TYP_K_SUBJECT_TYPE NUMBER (6)) > > > > > > The function GET_HRC_LEVEL simply counts the depth of the tree_path > > > (returning an integer), I can provide it to you if needed. > > > Anyway, the error occurs even if I remove the 3rd column from the > > > select... > > > > > > I really don't understand where the error is: the only way to make it > work > > > is to remove the "order by" and replace the 6th line with > > > "(SELECT rtrim(substr(C_TREE_PATH,1,10)) C_TREE_PATH" > > > but it's not what I need. > > > > > > Can you help me? > > > > Explanation: > > > > Like can not be handled as join-predicate, only as predicate in one > table > comparing columns of the same table/values. > > You use the like between two columns of two different tables. > > The kernel sees the only chance in doing a join using other > join-predicates (none given in your case --> cartesian join) to prepare > one > table and to check the like in this internally prepared temporary table. > > To be able to do everything needed, three long columns are needed in > that > table (and the two short ones for the final output): > > b.c_tree_path (4000 bytes) for ordering > > B.C_TREE_PATH (4000 bytes) for checking the like > > (unfortunately the first occurrence will not be re-used) > > A.C_TREE_PATH (1000 bytes) for checking the like > > --> more than around 8000 bytes in one row --> causing the trouble. > > The definition length is checked, not the length of actual stored > values. > > > > I did not find another select doing the same and not returning -2000. > > > > No, there are no plans for the near future to increase row length. > > > > Do you see any chance to shorten from 4000 to 3500? > > > > Elke > > SAP Labs Berlin > > > > > > > Thanks in advance > > > > > > Fabio Pinotti > > > > -- > > 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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
