> I think you can't use $cursor variable within recursive cursor declaration. > You ought to write it like this > CREATE DBPROC bom (IN main_item VARCHAR(25)) RETURNS CURSOR AS > BEGIN > DECLARE bom CURSOR FOR
This also gives the same error. > Are you sure that in the second select the where condition is sub = > B.main_item_code instead of main_item_code=B.sub_item_code? The RECURSIVE CURSOR statement is directly lifted from the SAP DB docs. Thanks for your response. ----- Original Message ----- From: "Matteo Gattoni" <[EMAIL PROTECTED]> To: "QA" <[EMAIL PROTECTED]> Sent: Monday, July 12, 2004 12:55 PM Subject: Re: Recursive DECLARE CURSOR statement > Hello! > I think you can't use $cursor variable within recursive cursor declaration. > You ought to write it like this > CREATE DBPROC bom (IN main_item VARCHAR(25)) RETURNS CURSOR AS > BEGIN > DECLARE bom 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 FRO suman.PX ORDER BY 1, 2; > END; > > Are you sure that in the second select the where condition is sub = > B.main_item_code instead of main_item_code=B.sub_item_code? > > Bye, > Matteo > > ************************************* > Matteo Gattoni > ICTeam S.p.A. > Via Azzano S.Paolo, 139 > 24050 GRASSOBBIO (BG) > Tel.: +39 035 4232156 > Fax: +39 035 4522034 > e-mail: [EMAIL PROTECTED] > ************************************* > ----- Original Message ----- > From: "QA" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, July 10, 2004 12:11 PM > Subject: Recursive DECLARE CURSOR statement > > > > 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] > > > > > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
