Results 1 to 3 of 3

Thread: Db2 As400 Sp

  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Unanswered: Db2 As400 Sp

    Hi There

    I am trying to write a Stored Procedure which
    fires one inner query in the loop of outer query resultset. Finally i want to return one resultset carrying the data from both outer and inner query.

    This is the code and when i try to compile it, it gives me Error: "The keyword DECLARE was not expected here. A syntax error was detected at keyword DECLARE." at DECLARE CUR1 position.


    P1: BEGIN NOT ATOMIC
    DECLARE Cust_no DECIMAL(7,0);
    DECLARE Cust_name Char(30);
    DECLARE Item_Descr Char(30);
    DECLARE Cust_Item_No Char(20);
    DECLARE Pack_no DECIMAL(5,0);
    DECLARE Size_no DECIMAL(7,2);
    DECLARE UnitOM Char(2);
    DECLARE List_price DECIMAL(7,2);
    DECLARE AV_Movement DECIMAL(7,0);
    DECLARE Week_Movement DECIMAL(7,0);
    DECLARE QO_Hand DECIMAL(7,0);
    DECLARE CustLoc_no DECIMAL(3,0);

    DECLARE Total_Deal DECIMAL(7,0);
    DECLARE Net_Cost DECIMAL(7,0);
    DECLARE Deal_PCT DECIMAL(7,0);
    DECLARE Start_Date DECIMAL(9,0);
    DECLARE End_Date DECIMAL(9,0);
    DECLARE Deal_EXCL Char(1);
    DECLARE ENDTABLE2 INT DEFAULT 0;
    DECLARE ENDTABLE3 INT DEFAULT 0;

    SET ENDTABLE2 = 1;

    SET ENDTABLE3 = 1;


    create table qtemp.temp(Cust# DECIMAL(7,0), custname CHAR(30), CustItemN CHAR(20), Pack DECIMAL(5,0),
    Size DECIMAL(7,2), UOM CHAR(2), ListPrize DECIMAL(7,2), TotalDealAmt DECIMAL(7,2),
    NetCost DECIMAL(7,2), TotalDealPer DECIMAL(7,2), AWMovement DECIMAL(7,0), QOHand DECIMAL(7,0),
    DealSTDate DECIMAL(9,0), DealEndDate DECIMAL(9,0), ItemDesc CHAR(30));

    DECLARE CUR1 CURSOR WITH RETURN FOR
    SELECT A.IDESCR FROM FMGDATA.ITEMFL A, FMGDATA.SUPPLOC B WHERE A.ISUPP# = SUPPN
    AND A.IITEM# = ITEMN AND A.ISUPP# = B.SUPP# AND B.SUPPL# = 0 AND B.SLPRIV <> 'P' ;
    Open CUR1;
    FETCH CUR1
    INTO Item_Descr;
    Close CUR1;


    DECLARE CUR2 CURSOR WITH RETURN FOR
    SELECT A.CUST# AS CUST#, B.CNAME AS CUSTOMERNAME,
    A.CICITM AS CUSTOMERITEM#, A.CIPACK AS PACK, A.CISIZE AS SIZE, A.CISUOM AS UM,
    A.CI$LST AS LISTPRICE, A.CI52WM AS AVEWEEKLYMOVEMENT, A.CIQWKM AS WEEKMVM,
    A.CIQONH AS QUANTITYONHAND, A.CUSTL# FROM FMGDATA.CUSTITM A,
    FMGDATA.CUSTLOC B WHERE A.ISUPP# = SUPPN AND A.IITEM# = ITEMN
    if REGION <>'ALL'
    THEN
    AND B.CLREGN = REGION
    End if;
    AND A.CUST# = B.CUST# AND B.CUSTL# = 0 AND A.CI$LST <> 0
    ORDER BY CUST#;

    Open CUR2;
    FETCH CUR2
    Into Cust_no, Cust_name, Cust_Item_No, Pack_no, Size_no, UnitOM, List_price, AV_Movement, Week_Movement, QO_Hand, CustLoc_no;

    DECLARE CUR3 CURSOR WITH RETURN FOR
    SELECT A.DE$SAV AS TOTALDEAL$, A.DE$UNT AS NETCOST, A.DEPSAV* 100 AS DEALPCT,
    A.DESTRD AS STARTDATE , A.DEENDD AS ENDDATE, B.CDEXCL, A.DECUST, C.CUSEDL FROM
    FMGDATA.DEALFLJ A LEFT OUTER JOIN FMGDATA.CUSTEXC B ON A.DECUST = B.SCUST#
    AND B.BCUST# = ICUSTN, FMGDATA.CUSTFL C WHERE A.DECUST = Cust_no
    AND A.DESUPP = SUPPN
    AND A.DEITEM = ITEMN
    AND A.DECUSL = CustLoc_no
    AND A.DECITM = Cust_Item_No
    AND A.DECUST = C.CUST#
    Open CUR3;
    FETCH CUR3

    Into Total_Deal, Net_Cost, Deal_PCT, Start_Date, End_Date, Deal_EXCL;

    If Deal_EXCL <> 'Y' then
    INSERT INTO temp
    (Cust#, custname, CustItemN, Pack, Size, UOM, ListPrize, TotalDealAmt, NetCost, TotalDealPer, AWMovement, QOHand, DealSTDate, DealEndDate, ItemDesc)
    VALUES( Cust_no, Cust_name, Cust_Item_No, Pack_no, Size_no, UnitOM, List_price,
    Total_Deal, Net_Cost, Deal_PCT, AV_Movement, QO_Hand, Start_Date, End_Date, Item_Descr);
    End if;
    WHILE ENDTABLE3 = 0 DO
    Close CUR3;
    WHILE ENDTABLE2 = 0 DO
    Close CUR2;

    DECLARE FinalCur CURSOR WITH RETURN FOR
    Select * from temp ORDER BY CUST#;
    drop table qtemp.temp;
    Open FinalCur;

    End P1



    Please Help

    Keshav

  2. #2
    Join Date
    Jan 2003
    Posts
    1

    Re: Db2 As400 Sp

    I think you have to separate the declare statements from the other statements.
    (

    DECLARE ...
    DECLARE ...

    SET ...
    SET ...

    ---YOU CANNOT PLACE ANY DECLARE STMs HERE
    )

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Re: Db2 As400 Sp

    HI
    I have some problems with DB2 connection
    Can u show me the way that i can connect to DB2 Server and load data from DB2 Client to DB2 Server through using codes of Visual Basic
    Thank very math
    Last edited by thang; 11-09-03 at 21:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •