PROCEDURE WITH - WHILE DO STRUCTURE

[KeithRichards]

I’m REALLY hoping someone can point me in the right direction with this procedure.

I am using a While Do control structure to run a select statement based on the ID number of the database row. Simply, while the row ID = 1 then I want to run the first select statement which contains the function called book_value. When the ID = 2 run the second select statement containing the book_value_2 function.

The aim is to return 4 columns of data ID, CODE, FORECAST_NUMBER, LIST_PRICE and RESULT for each of the rows in the DB.

Currently when I call the procedure I am getting the error below.

Error Code : 1109
Unknown table ' test.table ' in field list
(0 ms taken).

Thanks a million! Keifey

DELIMITER $$;

DROP PROCEDURE IF EXISTS `test`.`test_proc`$$

CREATE DEFINER=`root`@`%` PROCEDURE `test_proc`()
BEGIN
DECLARE TEST_VALUE decimal(6,4);

WHILE test.table.ID = '1' DO
SET TEST_VALUE = (SELECT ID, CODE, FORECAST_NUMBER, LIST_PRICE, (FORECAST_NUMBER + book_value(CODE) – LIST_PRICE) AS RESULT
FROM test.table WHERE ID = '1');

WHILE test.table.ID = '2' DO
SET TEST_VALUE = (SELECT ID, CODE, FORECAST_NUMBER, LIST_PRICE, (FORECAST_NUMBER + book_value_2(CODE) – LIST_PRICE) AS RESULT
FROM test.table WHERE ID = '2');


END WHILE;
END$$

DELIMITER ;$$

 

[r937]

could you please show some rows of sample data in this test.table, and then show the results that you want this procedure to produce

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[KeithRichards]

Hi r937,

Thanks for your reply.

I would attached a sample in excel but I'm not sure if that is possible? Anyway, I have included a small data sample and some commentary below.The main aim is to calculate the RESULT column below.

1. The first book_value function in the procedure is based on a an actual value in table 1.

Table 1

CODE ID book_value
T005 0 $ 22.77


Table 2

CODE ID FORECAST_NUMBER LIST_PRICE RESULT
T005 1 $3.00 ..................... $1.80.......... $23.97
T005 2 $3.27 ..................... $1.85.......... $25.40
T005 3 $3.54 ..................... $1.89.......... $27.04
T005 4 $3.80 ..................... $1.94.......... $28.91
T005 5 $4.07 ..................... $1.99.......... $30.99


2. The numbers in the RESULT column in table 2 don't exist and need to be calculated. They are kicked off by using the book value function (0 ID) in in the procedure. To calculate the RESULT column for (ID 1-5) in table_2 requires iteration through FORECAST_NUMBER + (previous) RESULT - LIST_PRICE. Each result is the sequentially calculated using the previous RESULT.

in excel the formula for each cell would look like this.With G column being FORECAST_NUMBER, I column being (previous) RESULT and H column being LIST_PRICE. Notice I4 is based on the number in table 1 to kick off the sequence.

G5+I4-H5
G6+I5-H6
G7+I6-H7
G8+I7-H8
G9+I8-H9


Hope I haven't made things more complicated!

 

[r937]

Unknown table ' test.table ' in field list

which table is this test.table

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[KeithRichards]

Sorry to not make that clear.

test.table would = table 2 in the data example I just sent through.

Cheers,

K

 

[r937]

okay, that explains the reasoning behind the "while" in your attempted procedure

you need to rewrite this to use a cursor

see

http://dev.mysql.com/doc/refman/5.0/en/cursors.html

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[KeithRichards]

Awesome!

I'll do some head scratching and then re-write it and see how I go.

Cheers,

K

 

[KeithRichards]

Hi there,

I have done some research on cursors and it has been good to expand the knowledge. I have been googling for a few days and I still can't find any examples that help... so I am still stuck!

In the code below I have tried to merge what I originally had (in the earlier post) with the cursor syntax. Is someone able to give me any tips on where I am going wrong please? I can raise a new thread if necessary.

DELIMITER $$;

DROP PROCEDURE IF EXISTS `test`.`DemoCurs5`$$

CREATE PROCEDURE `test`.`DemoCurs5` ()
BEGIN

DECLARE d INT DEFAULT 0;
    DECLARE ID INT;
    DECLARE CODE VARCHAR(10);
    DECLARE FORECAST_NUMBER, LIST_PRICE, RESULT DECIMAL (20,4);
    DECLARE cur CURSOR FOR SELECT * FROM test.table;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;

OPEN cur;
    lbl: LOOP
    IF d=1 THEN
    LEAVE lbl;
    END IF;
    IF NOT d=1 THEN

WHILE test.table.ID = '1' DO (SELECT ID, CODE, FORECAST_NUMBER, LIST_PRICE, (FORECAST_NUMBER + book_value(CODE) - LIST_PRICE) AS RESULT
FROM test.table WHERE ID = '1');

FETCH cur INTO ID, CODE, FORECAST_NUMBER, LIST_PRICE, RESULT;

WHILE test.table.ID = '2' DO (SELECT ID, CODE, FORECAST_NUMBER, LIST_PRICE, (FORECAST_NUMBER + book_value_2(CODE) - LIST_PRICE) AS RESULT
FROM test.table WHERE ID = '2');

FETCH cur INTO ID, CODE, FORECAST_NUMBER, LIST_PRICE, RESULT;

END WHILE;
END LOOP;
CLOSE cur;

END$$

DELIMITER ;$$