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]
>
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
