Don't know anymore specifics though.
Anjo.
Kris Austin-Murray wrote:
disregard the first SELECT sent. was given the wrong query. *this* ugly
query is the one that fails both in the reporting tool and in sqlplus
(makes sense now):
SELECT
FORM_CD,
SUBMIT_DT,
MAX(DISTINCT DECODE(Name, '01_First_Name', Value, NULL)) First_Name,
MAX(DISTINCT DECODE(Name, '02_Last_Name', Value, NULL)) Last_Name,
MAX(DISTINCT DECODE(Name, '03_Street_Address', Value,
NULL)) Street_Address,
MAX(DISTINCT DECODE(Name, '04_City', Value, NULL)) City,
MAX(DISTINCT DECODE(Name, '05_State', Value, NULL)) State,
MAX(DISTINCT DECODE(Name, '06_Zip_Code', Value, NULL)) Zip_Code,
MAX(DISTINCT DECODE(Name, '07_Email', Value, NULL)) Email,
MAX(DISTINCT DECODE(Name, '08_REF', Value, NULL)) Referred_By,
MAX(DISTINCT DECODE(Name, '09_Newsletter', Value, NULL)) Newsletter,
MAX(DISTINCT DECODE(Name, '10_Q1', Value, NULL)) Q1,
MAX(DISTINCT DECODE(Name, '11_Q2', Value, NULL)) Q2,
MAX(DISTINCT DECODE(Name, '12_Q3', Value, NULL)) Q3,
MAX(DISTINCT DECODE(Name, '13_Q4', Value, NULL)) Q4,
MAX(DISTINCT DECODE(Name, '14_Q5', Value, NULL)) Q5,
MAX(DISTINCT DECODE(Name, '15_Q6', Value, NULL)) Q6,
MAX(DISTINCT DECODE(Name, '16_Q7', Value, NULL)) Q7,
MAX(DISTINCT DECODE(Name, '17_Q8', Value, NULL)) Q8,
MAX(DISTINCT DECODE(Name, '18_Q9', Value, NULL)) Q9,
MAX(DISTINCT DECODE(Name, '19_Q10', Value, NULL)) Q10,
MAX(DISTINCT DECODE(Name, '20_Q11', Value, NULL)) Q11,
MAX(DISTINCT DECODE(Name, '21_Q12', Value, NULL)) Q12,
MAX(DISTINCT DECODE(Name, '22_Q13', Value, NULL)) Q13,
MAX(DISTINCT DECODE(Name, '23_Q14', Value, NULL)) Q14,
MAX(DISTINCT DECODE(Name, '24_Q15', Value, NULL)) Q15,
MAX(DISTINCT DECODE(Name, '25_Q16', Value, NULL)) Q16,
MAX(DISTINCT DECODE(Name, '26_Q17', Value, NULL)) Q17,
MAX(DISTINCT DECODE(Name, '27_Q18', Value, NULL)) Q18,
MAX(DISTINCT DECODE(Name, '28_Q19', Value, NULL)) Q19,
MAX(DISTINCT DECODE(Name, '29_Q20', Value, NULL)) Q2 0,
MAX(DISTINCT DECODE(Name, '30_Q21', Value, NULL)) Q21,
MAX(DISTINCT DECODE(Name, '31_Q22', Value, NULL)) Q22
FROM value, entry
WHERE value.entry_id = entry.entry_id AND entry.form_cd =
'allure_beautyballot2002_mps'
GROUP BY FORM_CD, SUBMIT_DT, value.entry_id
/
SQL> @run_for_robert.sql
FROM value, entry
*
ERROR at line 35:
ORA-01467: sort key too long
terrible query. i know.
i found this on metalink:
64K Restriction
~~~~~~~~~~~~~~~
The 64K restriction is a maximum internal buffer size for sort
keys. This can affect any sort operation, including join keys
used in sort-merge-join operations. For example, if a statement
joins on a number of VARCHAR2(4000) columns , or group by on
a number of VARCHAR2(4000) columns then this limit can be hit.
In most cases the limit is hit at run time and so is data
dependent, but some operatio ns do use a maximum data size.
is there any solutions out there?
thanks!
kris
On Fri, 2 Aug 2002, Kris Austin-Murray wrote:hi
this error is being generated in an admin report tool. the query is the
following:
SQL> SELECT value.name, value.value, entry.submit_dt
2 FROM value, entry WHERE value.entry_id = entry.entry_id AND
3 entry.form_cd = 'glamour_injeanious_mps';
here are the global settings for sqlplus:
SQL> show maxdata
maxdata 6000
SQL> show arraysize
arraysize 1
this began occuring when we change one field from 2000 characters, to
4000:
SQL> desc value
Name Null? Type
----------------------------------------- ----------------------------
ENTRY_ID NOT NULL NUMBER(10)
NAME NOT NULL VARCHAR2(100)
VALUE VARCHAR2(4000)
the total # of records in value:
SQL> select count(*) from value;
COUNT(*)
----------
16408559
i've played with setting the arraysize, maxdata without luck. this query
does return results from within sqlplus, but not for the reporting tool,
which sits on sun solaris 2.5 box with 4 GIG of memory.
we're running Oracle8i Enterprise Edition Release 8.1.6.0.0 on sun solaris
2.6
i've searched metalink and found a doc (Note:1012366.6) that explains the
ORA-1467 error. still i am without a solution.
any help out there?
thank you!
kris
+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
kris austin-murray ... senior database manager, ocp ... advance internet
www.advance.net ... [EMAIL PROTECTED] ... 201-459-2805
+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
"Darkness cannot drive out darkness; only light can do that. Hate
cannot drive out hate; only love can do that." - Martin Luther King, Jr.
+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
kris austin-murray ... senior database manager, ocp ... advance internet
www.advance.net ... [EMAIL PROTECTED] ... 201-459-2805
+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
"Darkness cannot drive out darkness; only light can do that. Hate
cannot drive out hate; only love can do that." - Martin Luther King, Jr.
