Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: Question on VIEW creation...

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2007
    Posts
    2

    Great...

    Quote Originally Posted by stolze
    Maybe I'm missing some crucial piece of information...
    No, you're not missing anything.. I'm just a novice - that should work just fine.

    Danke scho:n, mein Herr!

Posting Permissions

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