Recursive DECLARE CURSOR statement

[QA]

Hi All,

Can anybody provide a solution to my problem with RECURSIVE DECLARE
STATEMENT.

I am trying to explode bill of materials using Recursive DECLARE CURSOR
statement explained in docs. Following is the stored procedure-

CREATE DBPROC bom (IN main_item VARCHAR(25)) RETURNS CURSOR AS

     $CURSOR = 'bom';

     BEGIN

     DECLARE :$CURSOR CURSOR FOR
     WITH RECURSIVE PX (main, sub, qty, super_main) AS
        (SELECT main_item_code, sub_item_code, sub_item_qty,  main_item_code
          FROM suman.mfg_latest_bom_v WHERE main_item_code = :main_item
         UNION ALL
           SELECT main_item_code, sub_item_code, sub_item_qty, super_main
           FROM suman.mfg_latest_bom_v B, suman.PX
           WHERE sub = B.main_item_code)
        SELECT super_main, sub, qty FROM suman.PX ORDER BY 1, 2;

 END;

While creating the above-mentioned procedure in SQL Studio I face the
foloowing error-

 General error;-9205 POS(80) System error: AK Catalog information not
 found:FF000006000A0020008900.

 (DECLARE keyword is highlighted in SQL Studio).

 This is the view definition-

 CREATE OR REPLACE VIEW mfg_latest_bom_v
  (bom_version, main_item_code, sub_item_code, sub_item_qty,
 bom_item_remarks) AS
    SELECT bom_version, main_item_code, sub_item_code, sub_item_qty,
 bom_item_remarks
    FROM mfg_bom_m
    WHERE bom_version = (SELECT MAX(bom_version) FROM mfg_bom_m M
                     WHERE M.main_item_code = mfg_bom_m.main_item_code)

 and this is the underlying table-

 create table mfg_bom_m(
     bom_version  numeric(2, 0),
     main_item_code  varchar(25),
     sub_item_code  varchar(25),
     sub_item_qty  numeric(12, 6),
     bom_item_remarks varchar(60),
     constraint mfg_bom_m_pk primary key(main_item_code, sub_item_code,
 bom_version));


DB Version: 7.5.00.15 / 7.4.00.30
Mode: Internal
Platform- Win2K SP4 / SP1.

Thanks,




-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]