news wrote:
>
> Hi Thomas,
>
> It does not work.
>
That is a very short info. May we ask what is going on, which error occurs or if there
are not the results expected or what do you mean with 'It does not work'?
Maybe it is this we found out in the meantime:
error Owner must be specified comes if
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;
Is used
Workaround: use
CREATE TABLE TEMP.bom_view
(
main_item_code varchar(25),
sub_item_code varchar(25),
sub_item_qty numeric(12, 6),
bom_version numeric(2, 0),
constraint mfg_bom_m_pk primary key(main_item_code, sub_item_code,
bom_version));
insert TEMP.bom_view
SELECT main_item_code, sub_item_code, sub_item_qty, bom_version
FROM suman.mfg_latest_bom_v;
instead
Elke
SAP Labs Berlin
>
> 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]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
