AW: Error Create Procedure

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:rusanov@xxxxxxxxxxxx] 
>Gesendet: Montag, 25. Oktober 2004 08:17
>An: maxdb@xxxxxxxxxxxxxxx
>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/maxdb?unsub=thomas.anhaus@xxxxxxx
>

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx