stored procedure to create

laknar · #1 (**permalink**) 03-24-09, 03:05

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.

ARWinner · #2 (**permalink**) 03-24-09, 08:42

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

tonkuma · #3 (**permalink**) 03-24-09, 15:18

1)

Quote:

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')
;

Internet Services

Web Development

Digital Marketing

Consumer Tech