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]
