using a stored procedure to compare tables

[Thanos]

Hello, Im a novice at this so bare with me.

I have two tables which will be match merged based on 10 conditions
(select statements). For example, if a record exists in table A but
not in Table B then that record will be inserted into a separate audit
table called Table C lets say! So based on this 10 conditions or
select statements that I wrote I would like to create a stored
procedure but im not sure how to begin. Any help would be great. I
don't think that there will be an parameters. Any examples or help
would be greating appreciated Thanks,

WofD

[Serge Rielau]

You post is very generic...
Of course you can write a procedure.
You may also be able to use regular SQL.
Can't say more without knowing more of tehe details.

Cheers
Serge

[Thanos]

Hello Serge,

Okay here is the details. Im using db2 udb v7.1 for windows.

i have 4 tables lets call them, table1 table2 table3 exception4

table1 and table2 have the same columns as follows
REC_TYPE, EMP_NO, HIRE_DATE, DEPART_ID
However, table1 also has the following columns, GROUP_SRC, EMP_GROUP,
EXEMP, INEMP.

There are all chars except hire_date. Also EXEMP and INEMP are either
'Y' or 'N'

Table3 will be inserted with records which match or not-match through
various join statements with multiple where clauses.
i.e insert into table3(t_REC_Type, t_EMP_NO, effect_date, t_depart_id)
select table1.rec_type....blah balh blah
from table1 inner join table2 on table1.emp_no = table2.emp_no
where (now this is problem #1 i need the records to be insert for the
when EXEMP = 'Y' and INEMP = 'N', or when EXEMP = 'N' and INEMP = 'Y',
or when EXEMP = 'N' and INEMP = 'N, or when EXEMP = 'Y' and INEMP =
'Y') so pretty much the different combination of Y and N for the two
fields.

So how would you write such a statement only once instead of 4 times.

Problem #2 how do you write a stored procedure for this. As this
have to be automated on a monthly basis???

Thanks,

WofD

[Serge Rielau]

Sorry.. still not enough info.
1. If for INEMP and EXEMP you look at all combinations and always
insert. Why do you look at them at all?
2. You didn't talk at about that exception table
3. Maybe I'm missing something obvious. But if you can write it
interactively what is the issue with the procedure. Is it that the
conditions chaange?

Maybe some example data would be in order?

Cheers
Serge

[Thanos]

okay let me try this again. After some more thought i figured this is
would i would like to do. I would like to create a stored procedure
to place data into a temporary table from a right outter join between
TAB1 and TAB2

I would like to create a stored procedure as follows. This is my
pseudo create procedure...

CREATE PROCEDURE INSERT_INTO_TAB3
SPECFIC INSERT_INTO_TAB3
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE
temp_TABLE (empon char(10), col2 char (1)..)
NOT LOGGED ON COMMIT PRESERVE ROWS;

INSERT INTO TEMP_TAB2_SELECT TAB1.EMPON, TAB1.COL2 FROM TAB1 RIGHT
OUTER JOIN TAB2 on TAB1.EMPON = TAB2;
END
=========================================
Now would this work in so far as to populate the temp_table with the
insert statement??? If the above does work then the next part of the
procedure would be to to the query the temporary table for those
records in which col2 = 'Y' and insert those records into another
table called TAB3 and if col2 = 'N' then insert into another table
called TAB4. Any ideas???

Thanks

[Serge Rielau]

Yes, absolutely. That will work.

Cheers
Serge