Thanks for mailing the problem. It's is a bug which is caused by the fact that rt_v_all_devicetype4test is a complex view. We will fix this with one of the next releases.
Best Regards Thomas >-----Urspr�ngliche Nachricht----- >Von: rusanov [mailto:[EMAIL PROTECTED] >Gesendet: Montag, 25. Oktober 2004 08:17 >An: [EMAIL PROTECTED] >Betreff: Error Create Procedure > > >Hello I have problem when I create below procedure > >if i change name view LAB.rt_v_all_devicetype4test on other >name procedure >create succeed > >CREATE DBPROC LAB.list_all_devicetype4test >( IN test INT >) >RETURNS CURSOR >AS >BEGIN >TRY >$CURSOR = 'LIST_DTT_CURSOR'; >DECLARE :$CURSOR CURSOR >FOR >SELECT >* >FROM >LAB.rt_v_all_devicetype4test >WHERE >test = :test >ORDER BY >devicetype_name; >CATCH >IF $rc <> 100 THEN >STOP($rc, $errmsg); >END; > >Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed >General error;-9111 POS(135) System error: Move error. >CREATE DBPROC LAB.list_all_devicetype4test >( IN test INT >) >RETURNS CURSOR >AS >BEGIN >TRY >$CURSOR = 'LIST_DTT_CURSOR'; >DECLARE :$CURSOR CURSOR >FOR >SELECT >* >FROM >LAB.rt_v_all_devicetype4test >WHERE >test = :test >ORDER BY >devicetype_name; >CATCH >IF $rc <> 100 THEN >STOP($rc, $errmsg); >END; > >my database > >CREATE TABLE LAB.rt_producers >( > producer INT NOT NULL DEFAULT SERIAL(1), > > PRIMARY KEY (producer), > > producer_name VARCHAR (100) NOT NULL, > producer_desc VARCHAR (255), > > CONSTRAINT unique_name > UNIQUE (producer_name), > > > status INT DEFAULT 0, > > created TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > deleted TIMESTAMP, > > modifier VARCHAR (32) NOT NULL DEFAULT USER >) > >CREATE TABLE LAB.rt_devicetypes >( > device_type INT NOT NULL DEFAULT SERIAL(1), > > > PRIMARY KEY (device_type), > > producer INT NOT NULL, > > FOREIGN KEY REF2PRODUCERS (producer) > REFERENCES LAB.rt_producers (producer), > > devicetype_name VARCHAR(100) NOT NULL, > devicetype_desc VARCHAR(255), > > > status INT NOT NULL DEFAULT 0, > > created TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > deleted TIMESTAMP, > > modifier VARCHAR(32) NOT NULL DEFAULT USER >) > >/* > views >*/ > >/* > LAB.rt_v_all_devicetypes >*/ > >CREATE VIEW LAB.rt_v_all_devicetypes > >AS > >SELECT > > a.device_type, > a.devicetype_name, > a.devicetype_desc, > > b.producer, > b.producer_name > >FROM > LAB.rt_devicetypes a, > LAB.rt_producers b >WHERE > a.producer = b.producer > > >CREATE TABLE LAB.rt_laboratories >( > laboratory INT NOT NULL DEFAULT SERIAL(1), > > PRIMARY KEY(laboratory), > > laboratory_name VARCHAR(100) NOT NULL, > laboratory_desc VARCHAR(255) NULL, > > > status INT NOT NULL DEFAULT 0, > > created timestamp NOT NULL DEFAULT TIMESTAMP, > modified timestamp NOT NULL DEFAULT TIMESTAMP, > deleted timestamp NULL, > > modifier VARCHAR(32) NOT NULL DEFAULT USER >) > >CREATE TABLE LAB.rt_units >( > unit INT NOT NULL DEFAULT SERIAL(1), > > PRIMARY KEY (unit), > > > unit_name VARCHAR (100) NOT NULL, > unit_desc VARCHAR (255) NULL, > > > status INT NOT NULL DEFAULT 0, > > created TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > deleted TIMESTAMP, > > modifier VARCHAR (32) NOT NULL DEFAULT USER >) > >CREATE TABLE LAB.rt_tests >( > test INT NOT NULL DEFAULT SERIAL(1), > > PRIMARY KEY (test), > > /* > test_type > > 0 - simple > 1 - complex > > 2 - in complex > > */ > > test_type INT NOT NULL, > > CONSTRAINT test_type IN (0, 1, 2), > > > test_code VARCHAR (50) NOT NULL, > > /* > laboratory > */ > > laboratory INT NOT NULL, > > FOREIGN KEY ref2laboratories (laboratory) > REFERENCES LAB.rt_laboratories (laboratory), > > /* > CONSTRAINT unique_analitcode > UNIQUE (laboratory, test_code), > */ > > > test_name VARCHAR (100) NOT NULL, > test_desc VARCHAR (255) NULL, > > /* > unit > */ > > unit INT NULL, > > FOREIGN KEY ref2units (unit) > REFERENCES LAB.rt_units (unit), > > > status INT DEFAULT 0, > > created TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > deleted TIMESTAMP, > > modifier VARCHAR (32) NOT NULL DEFAULT USER >) > >CREATE VIEW LAB.rt_v_all_tests > >AS > >SELECT > > b.test, > > b.test_type, > > b.test_code, > b.test_name, > b.test_desc, > > b.status, > > c.unit, > c.unit_name, > > a.laboratory, > a.laboratory_name > >FROM > LAB.rt_laboratories a, > LAB.rt_tests b, > LAB.rt_units c >WHERE > a.laboratory = b.laboratory > AND b.unit = c.unit (+) > >CREATE TABLE LAB.rt_devicetype4test >( > devicetype_test INT NOT NULL DEFAULT SERIAL(1), > > PRIMARY KEY(devicetype_test), > > > device_type INT NOT NULL, > > FOREIGN KEY ref2devicetypes (device_type) > REFERENCES LAB.rt_devicetypes (device_type), > > > test INT NOT NULL, > > FOREIGN KEY ref2tests (test) > REFERENCES LAB.rt_tests (test), > > > status INT NOT NULL DEFAULT 0, > > created TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP, > deleted TIMESTAMP NULL, > > modifier VARCHAR(32) NOT NULL DEFAULT USER >) > >CREATE VIEW LAB.rt_v_all_devicetype4test > >AS > >SELECT > > c.devicetype_test, > > a.test, > a.test_type, > a.test_code, > a.test_name, > a.test_desc, > > b.device_type, > b.devicetype_name > >FROM > LAB.rt_devicetype4test c, > LAB.rt_v_all_tests a, > LAB.rt_v_all_devicetypes b > >WHERE > c.test = a.test > AND c.device_type = b.device_type > AND c.status = 0 > > >-- >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]
