Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: stored procedure to create

    Code:
    --------------------------------------------------
    -- Create Table TEST1
    --------------------------------------------------
    Create table TEST1 (
        JOB                            VARCHAR(50)                     ,
        KLI                            VARCHAR(50)                     ,
        VDATE                          DATE                            ,
        STAT                           VARCHAR(50)                     ,
        FTYPE                          VARCHAR(50)                     ) 
    in USERSPACE1   ;
    
    Insert into TEST1 values('D', 'HH', '2009-03-24', 'W', 'I');
    Insert into TEST1 values('G', 'HH', '2009-03-24', 'W', 'D');
    Insert into TEST1 values('G', 'HH', '2009-03-24', 'W', 'F');
    Insert into TEST1 values('G', 'HH', '2009-03-24', 'W', 'G');
    Insert into TEST1 values('N', 'HH', '2009-03-24', 'W', 'D');
    Insert into TEST1 values('N', 'HH', '2009-03-24', 'W', 'F');
    Insert into TEST1 values('N', 'HH', '2009-03-24', 'W', 'G');
    Insert into TEST1 values('R', 'HH', '2009-03-24', 'W', 'L');
    Insert into TEST1 values('S', 'HH', '2009-03-24', 'W', 'A');
    Insert into TEST1 values('T', 'HH', '2009-03-24', 'W', 'D');
    Insert into TEST1 values('T', 'HH', '2009-03-24', 'W', 'F');
    Insert into TEST1 values('T', 'HH', '2009-03-24', 'W', 'G');
    If STAT='S' Where FTYPE='A' AND JOB='S' Then Update STAT='R' WHere JOB='T';

    IF STAT ='S' WHERE JOB='T' then Update STATS ='R' WHere JOB='N' for the corresponding FTYPE;

    for the job 'N' and FTYPE ='F' is dependent on both FTYPE 'F' and 'G" in JOB='T' i.e(both the JOB 'T' STAT should be 'S' before updating 'R' WHere JOB='N' for the corresponding FTYPE='F'

    IF STAT ='S' WHERE JOB='T' and FTYPE='D' then Update STATS ='R' WHere JOB='N' for the corresponding FTYPE

    For JOB 'G'
    IF STAT ='S' WHERE JOB='N' then Update STATS ='R' WHere JOB='G' for the corresponding FTYPES;

    How can i write the above in stored procedure.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Turn each of your statements into proper SQL update statements. Then look up the CREATE PROCEDURE statement to see how to put those update statements into it.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1)
    If STAT='S' Where FTYPE='A' AND JOB='S' Then Update STAT='R' WHere JOB='T';
    I couldn't understand this.
    Because If JOB='S', then the row sould not satidfy the condition "WHere JOB='T'".

    Or, do you want if there is a row with STAT='S' Where FTYPE='A' AND JOB='S', then update all rows WHere JOB='T'?

    2) You had better to include the data which satisfy your requirements.
    For example, rows with STAT='S',
    Then show me(us?) which rows to update for each conditions.

    3) What platform and version do you use?
    If you are using DB2 for LUW, you can specify multiple rows in a VALUES statement. Like this:
    Code:
    Insert into TEST1 values
     ('D', 'HH', '2009-03-24', 'W', 'I')
    ,('G', 'HH', '2009-03-24', 'W', 'D')
    ,('G', 'HH', '2009-03-24', 'W', 'F')
    ,('G', 'HH', '2009-03-24', 'W', 'G')
    ,('N', 'HH', '2009-03-24', 'W', 'D')
    ,('N', 'HH', '2009-03-24', 'W', 'F')
    ,('N', 'HH', '2009-03-24', 'W', 'G')
    ,('R', 'HH', '2009-03-24', 'W', 'L')
    ,('S', 'HH', '2009-03-24', 'W', 'A')
    ,('T', 'HH', '2009-03-24', 'W', 'D')
    ,('T', 'HH', '2009-03-24', 'W', 'F')
    ,('T', 'HH', '2009-03-24', 'W', 'G')
    ;

Posting Permissions

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