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]
