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]
