Hi Thomas, It does not work.
Anhaus, Thomas wrote: > QA wrote : > > > > >Hi Elke, > > > >Thanks for the valuable suggestion. > > > >I wrote the following stored procedure onlines of your advice- > > > >CREATE DBPROC shortlist_month (IN schedule VARCHAR(14), IN > >flag CHAR(1)) > >RETURNS CURSOR AS > > > > $CURSOR = 'bom'; > > > > BEGIN > > CREATE TABLE temp.bom_view > > (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 suman.mfg_bom_m > > WHERE bom_version = (SELECT MAX(bom_version) FROM suman.mfg_bom_m > >M > > WHERE M.main_item_code = mfg_bom_m.main_item_code); > > > > 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 temp.bom_view WHERE main_item_code IN ( > > SELECT schedule_plan_item FROM suman.ppc_schedule_m WHERE > >schedule_code = :schedule) > > UNION ALL > > SELECT main_item_code, sub_item_code, sub_item_qty, super_main > > FROM temp.bom_view B, suman.PX > > WHERE sub = B.main_item_code) > > > > SELECT sub AS sub_item_code, item_item_desc, item_mfg_flag, > >stock_item_qty, > > SUM(qty * schedule_plan_qty) AS req_qty, (stock_item_qty - SUM(qty > >* schedule_plan_qty)) AS short_qty > > FROM suman.PX B, suman.ppc_schedule_m S, suman.mfg_item_p I, > >suman.mfg_item_stock V > > WHERE B.super_main = S.schedule_plan_item > > AND schedule_code = :schedule > > AND I.item_mfg_flag = :flag > > AND B.sub = I.item_item_code > > AND B.sub = V.stock_item_code > > AND V.stock_dept_code = 'DP0008' > > GROUP BY sub, item_item_desc, item_mfg_flag, stock_item_qty > > HAVING SUM(qty * schedule_plan_qty) > 0 ORDER BY 3, 1; > > > > DROP TABLE TEMP.BOM_VIEW; > >END; > > > >This stored procedure compiles and works perfectly on MaxDB 7.5.00.15, > > > >But on SapDB 7.4.3.30 it compiles but at runtime gives error -4024 and > >if I put a try..catch in the stored procedure then it returns the error > >-4004. > > > >Why it is behaving like this? > > > > > > > >Zabach, Elke wrote: > > > >> QA wrote: > >> > >> > >> > Hi Elke, > >> > > >> > I just upgraded to 7.5.00.15 and still getting the same error. > >> > > >> > 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)); > >> > > >> > I am using default INTERNAL mode. <select * from > >mfg_latest_bom_v> works > >> > pretty fast, returns result within a fraction of second. > >Error occurs > >> > during > >> > definition of the dbproc. > >> > Platform- Win2K SP4. > >> > > >> > Thanks, > >> > > >> > >> > >> Hi, > >> > >> > >> The bug -9205 has to handled later. > >> A workaround (which at least was able to create dbproc, > >hoping that the > >> result is the wanted one) may be this: > >> > >> > >> 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 SUMAN.MFG_BOM_M > >> WHERE BOM_VERSION = (SELECT MAX(BOM_VERSION) FROM > >SUMAN.MFG_BOM_M M > >> WHERE M.MAIN_ITEM_CODE = > >MFG_BOM_M.MAIN_ITEM_CODE) > >> > >> > >> > >> CREATE DBPROC BOM (IN MAIN_ITEM VARCHAR(25)) RETURNS CURSOR AS > >> $CURSOR = 'bom'; > >> BEGIN > >> CREATE TABLE TEMP.BOM_VIEW > >> (CONSTRAINT MFG_BOM_M_PK PRIMARY KEY(MAIN_ITEM_CODE, > >SUB_ITEM_CODE, > >> BOM_VERSION)) > >> AS > >> SELECT MAIN_ITEM_CODE, SUB_ITEM_CODE, SUB_ITEM_QTY, > >BOM_VERSION > >> FROM SUMAN.MFG_LATEST_BOM_V; > >> 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 TEMP.BOM_VIEW WHERE MAIN_ITEM_CODE = :MAIN_ITEM > >> UNION ALL > >> SELECT MAIN_ITEM_CODE, SUB_ITEM_CODE, SUB_ITEM_QTY, > >SUPER_MAIN > >> FROM TEMP.BOM_VIEW B, SUMAN.PX > >> WHERE SUB = B.MAIN_ITEM_CODE) > >> SELECT SUPER_MAIN, SUB, QTY FROM SUMAN.PX ORDER BY 1, 2; > >> DROP TABLE TEMP.BOM_VIEW; > >> END; > >> > >> > >> > >> Hope this helps > >> > >> > >> Elke > >> SAP Labs Berlin > >> > >> > >> > > >> > ----- Original Message ----- > >> > From: "Zabach, Elke" <[EMAIL PROTECTED]> > >> > To: "'QA'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > >> > Sent: Monday, July 05, 2004 4:19 PM > >> > Subject: AW: Recursive DECLARE CURSOR statement > >> > > >> > > >> > > news wrote: > >> > > > > >> > > > Hi, > >> > > > > >> > > > 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; > >> > > > > >> > > > this gives me foloowing error- > >> > > > > >> > > > General error;-9205 POS(80) System error: AK Catalog > >information not > >> > > > found:FF000006000A0020008900. > >> > > > > >> > > > (DECLARE keyword is highlighted in SQL Studio). > >> > > > > >> > > > Any help is greatly appreciated. > >> > > > >> > > As I do not know what may have happened and not able to > >reproduce this > >> > effect: > >> > > Please upgrade to the newest version of the kernel > >(perhaps even to > >> > 7.5.00). > >> > > Please tell me/us if the problem remains, send me the > >> > table/view-definition of the table/view(s) > >(mfg_latest_bom_v sounds like > >> > a > >> > view, may be it is a little bit complicated and may cause > >the problem...) > >> > involved, tell us which sqlmode you are using (the error > >occures during > >> > DEFINITION of the dbproc, doesn't it?) if you can select > >the table in a > >> > pure > >> > way (select * from mfg_latest_bom_v) > >> > > > >> > > Thank you > >> > > > >> > > Elke > >> > > SAP Labs Berlin > >> > > > >> > > > > >> > > > > >> > > > -- > >> > > > 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] > >> > > I think in 7.4.03 we have problems with the :$cursor variable in combination with > UNION in the select statement. Please try to substitute the :$cursor variable by > its value : > > $CURSOR = 'BOM'; > BEGIN > CREATE TABLE temp.bom_view > (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 suman.mfg_bom_m > WHERE bom_version = (SELECT MAX(bom_version) FROM suman.mfg_bom_m M > WHERE M.main_item_code = mfg_bom_m.main_item_code); > > 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 temp.bom_view WHERE main_item_code IN ( > SELECT schedule_plan_item FROM suman.ppc_schedule_m WHERE > schedule_code = :schedule) > UNION ALL > SELECT main_item_code, sub_item_code, sub_item_qty, super_main > FROM temp.bom_view B, suman.PX > WHERE sub = B.main_item_code) > > > Please note that I used capital letters for $CURSOR ! > > HTH > Thomas > > > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
