Results 1 to 2 of 2

Thread: Db2 Script

  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: Db2 Script

    Hi


    I am pretty new to db2 and would like to get as much help as i can get


    All i want is to able to output the data from the arguments namely C1_ASSET_NUM, C1_SRV_DT, C1_ODOMTR_RDNG into a temp staging table and then export the data out in a flat file from that table.....


    This is the script below

    Thanks

    CREATE PROCEDURE SIEBEL.ISM1205_outbound ()
    SPECIFIC SIEBEL.Test1
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE C1_ASSET_NUM VARCHAR(100);
    DECLARE C1_SRV_DT CHAR(8);
    DECLARE C1_ODOMTR_RDNG INTEGER;
    DECLARE C1_INTEGRATION_ID VARCHAR(100);
    DECLARE C1_ATTRIB_12 VARCHAR(100);
    DECLARE C1_ATTRIB_14 VARCHAR(100);
    DECLARE C1_ATTRIB_07 VARCHAR(100);
    DECLARE C1_ATTRIB_41 VARCHAR(100);
    DECLARE at_end INT DEFAULT 0;
    DECLARE C1_BRANCH VARCHAR (4);
    DECLARE C1_LEASENUM VARCHAR(5);
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    ASSET_NUM
    --,B.SRV_DT
    --,B.ODOMTR_RDNG
    -- ,C.ROW_ID
    -- ,C.INTEGRATION_ID
    -- ,D.ATTRIB_12
    -- ,D.ATTRIB_14
    -- ,D.ATTRIB_07
    -- ,D.ATTRIB_41

    FROM
    SIEBEL.S_ASSET;
    ---- SIEBEL.S_VHCL_SRV B,
    ---- SIEBEL.S_ORG_EXT C,
    ------SIEBEL.S_VHCL_SRV_XM D

    --WHERE B.VHCL_ID = A.ROW_ID
    --AND D.PAR_ROW_ID = B.ROW_ID
    --AND C.ROW_ID = B.SRV_OU_ID;

    OPEN cursor1;

    FETCH cursor1 INTO C1_ASSET_NUM,C1_SRV_DT,C1_ODOMTR_RDNG,C1_INTEGRATI ON_ID,C1_ATTRIB_12,C1_ATTRIB_14,C1_ATTRIB_07, C1_ATTRIB_41;
    WHILE at_end = 0 DO

    SET C1_BRANCH = SUBSTR(C1_ASSET_NUM,1,4);

    END WHILE;
    CLOSE cursor1;

    END P1

  2. #2
    Join Date
    Sep 2003
    Posts
    8

    Re: Db2 Script

    I didn't get exactly what you are trying to explain..
    Are you trying to create a Global temporary table and to store the intermediate results and getting the final output?

    If yes,
    you can declare a temporary table within the proc.
    After opening and fetching the cursor, you insert the records into this tempory table. Before the statement END P1, you can write 'select * from temp table' to get the resultset..

    Hope this helps..

Posting Permissions

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