Question on VIEW creation...

depaulj · #1 (**permalink**) 05-15-07, 14:56

I have two nearly identical DB2 tables with the layouts as shown below.

The CODE_LOOKUP table contains only current codes and the CODE_LOOKUP_DELETED contains ONLY deleted codes (don't ask... legacy db requirements...

The application guarantees that the data is in either ONE or ther OTHER but not BOTH. When they delete, the app moves data from codes_lookup to codes_lookup_deleted - when they undelete the operation is reversed.

Anyway, we would like to build a VIEW to consolidate the data from both tables into a single view to include both deleted and active, for the benefit of the application reading this data. Would like the view to merge data from both tables and for every row found in the code_lookup_deleted we would like to set the DELETED_FLAG = Y in the VIEW (even though it's not found in the code_lookup_deleted table) - is that possible?! An example would be useful.

Here is the current table layouts:

code_lookup table:

sqltype sqllen sqlname.data sqlname.length
-------------------- ------ ------------------------------ --------------
496 INTEGER 4 CODE_LOOKUP_KEY 15
449 VARCHAR 30 CODE_TYPE 9
449 VARCHAR 200 CODE_DESC 9
449 VARCHAR 300 CODE_VALUE 10
393 TIMESTAMP 26 CREATE_TMSTP 12
453 CHARACTER 8 CREATE_USER 11
393 TIMESTAMP 26 UPDT_TMSTMP 11
453 CHARACTER 8 UPDT_USER 9
453 CHARACTER 1 DELETE_FLAG 11

code_lookup_deleted:

sqltype sqllen sqlname.data sqlname.length
-------------------- ------ ------------------------------ --------------
496 INTEGER 4 CODE_LOOKUP_KEY 15
449 VARCHAR 30 CODE_TYPE 9
449 VARCHAR 200 CODE_DESC 9
449 VARCHAR 300 CODE_VALUE 10
393 TIMESTAMP 26 CREATE_TMSTP 12
453 CHARACTER 8 CREATE_USER 11
393 TIMESTAMP 26 UPDT_TMSTMP 11
453 CHARACTER 8 UPDT_USER 9

Thanks,
James

stolze · #2 (**permalink**) 05-15-07, 15:56

Maybe I'm missing some crucial piece of information, but a simple UNION ALL does what you want:

Code:

SELECT code_lookup_key, code_type, code_desc, code_value
       create_tmstp, create_user, epdt_tmstmp, updt_user, delete_flag
FROM   code_lookup
UNION ALL
SELECT code_lookup_key, code_type, code_desc, code_value
       create_tmstp, create_user, epdt_tmstmp, updt_user, 'Y' AS delete_flag
FROM   code_lookup_deleted