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)) Q20,
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 operations 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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kris Austin-Murray
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
