Помогите переделать запрос, ora-600

LexMinsk
Дата: 17.03.2011 11:12:57
Всем привет.
Oracle 11.2g

При выполнении данного запроса:
INSERT INTO t_doc2x7xrest
 (restquantity,
  restquantitycopy,
  date_,
  accounttype,
  subaccantype,
  subaccancode)
 SELECT DISTINCT 0,
                 0,
                 CASE
                  WHEN relationtype IN
                       (SELECT docrelationtype
                          FROM cr_setupforaccrest
                         WHERE accounttype = :v_typeacc
                           AND isincrease = 1
                           AND subsystem = 1) THEN
                   docdate + 1
                  ELSE
                   docdate
                 END,
                 CASE
                  WHEN (subaccancode = 0 OR subaccancode = :v_natcurrcode) THEN
                   7
                  ELSE
                   17
                 END,
                 :i_subaccantype,
                 subaccancode
   FROM cr_contractsdocrelations
  WHERE contractid = :i_contractid
    AND relationtype IN
        (SELECT opertype
           FROM cr_opertypesetup
          WHERE (debetacctype = :v_typeacc OR creditacctype = :v_typeacc)
            AND subsystem = 1)
    AND subaccantype = :i_subaccantype
    AND subaccancode = (CASE
         WHEN :i_subaccantype = 0 THEN
          subaccancode
         ELSE
          :i_subaccancode
        END)
    AND docdate BETWEEN :i_begindate - 1 AND :i_enddate
    AND CASE
         WHEN relationtype IN (SELECT docrelationtype
                                 FROM cr_setupforaccrest
                                WHERE accounttype = :v_typeacc
                                  AND isincrease = 1
                                 AND subsystem = 1) THEN
         docdate + 1
         ELSE
          docdate
        END NOT IN (SELECT date_ FROM t_doc2x7xrest1)
;


Возникает:
 ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

Я так понял это из-за SELECT date_ FROM t_doc2x7xrest1.
LexMinsk
Дата: 17.03.2011 11:14:18
точнее в конце SELECT date_ FROM t_doc2x7xrest.
t_doc2x7xrest - это времянка.
LexMinsk
Дата: 17.03.2011 11:15:05
т.е. вот запрос
INSERT INTO t_doc2x7xrest
 (restquantity,
  restquantitycopy,
  date_,
  accounttype,
  subaccantype,
  subaccancode)
 SELECT DISTINCT 0,
                 0,
                 CASE
                  WHEN relationtype IN
                       (SELECT docrelationtype
                          FROM cr_setupforaccrest
                         WHERE accounttype = :v_typeacc
                           AND isincrease = 1
                           AND subsystem = 1) THEN
                   docdate + 1
                  ELSE
                   docdate
                 END,
                 CASE
                  WHEN (subaccancode = 0 OR subaccancode = :v_natcurrcode) THEN
                   7
                  ELSE
                   17
                 END,
                 :i_subaccantype,
                 subaccancode
   FROM cr_contractsdocrelations
  WHERE contractid = :i_contractid
    AND relationtype IN
        (SELECT opertype
           FROM cr_opertypesetup
          WHERE (debetacctype = :v_typeacc OR creditacctype = :v_typeacc)
            AND subsystem = 1)
    AND subaccantype = :i_subaccantype
    AND subaccancode = (CASE
         WHEN :i_subaccantype = 0 THEN
          subaccancode
         ELSE
          :i_subaccancode
        END)
    AND docdate BETWEEN :i_begindate - 1 AND :i_enddate
    AND CASE
         WHEN relationtype IN (SELECT docrelationtype
                                 FROM cr_setupforaccrest
                                WHERE accounttype = :v_typeacc
                                  AND isincrease = 1
                                 AND subsystem = 1) THEN
         docdate + 1
         ELSE
          docdate
        END NOT IN (SELECT date_ FROM t_doc2x7xrest)
;

AmKad
Дата: 17.03.2011 11:16:25
LexMinsk,

Представь данные, как показано здесь. Попробуем переписать.
env
Дата: 17.03.2011 11:19:02
LexMinsk,

explain plan for ..
LexMinsk
Дата: 17.03.2011 11:21:46
env,

я даже план не могу получить, ошибка вылезает
samatom
Дата: 17.03.2011 11:23:55
LexMinsk,

Insert идет в цикле или отдельный SQL-statement? Можно DDL для t_doc2x7xrest приложить
LexMinsk
Дата: 17.03.2011 11:29:26
samatom,

Инсерт отдельный.

CREATE GLOBAL TEMPORARY TABLE "TEST_ORA"."T_DOC2X7XREST" 
   (	"RESTQUANTITY" NUMBER(19,4), 
	"RESTQUANTITYCOPY" NUMBER(19,4), 
	"RESTPREV" NUMBER(19,4) DEFAULT 0, 
	"DATE_" TIMESTAMP (6), 
	"ACCOUNTTYPE" NUMBER(10,0), 
	"SUBACCANTYPE" NUMBER(10,0), 
	"SUBACCANCODE" NUMBER(10,0), 
	"UPDFLAG" NUMBER(10,0) DEFAULT 0, 
	"SIGNS" NUMBER(10,0) DEFAULT 0
   ) ON COMMIT PRESERVE ROWS ;
env
Дата: 17.03.2011 11:30:46
LexMinsk,

а в алерте и трейсе что?
LexMinsk
Дата: 17.03.2011 11:38:57
env,

alert.log достать не могу, он где то глубоко в UNIXe ((