Results 1 to 6 of 6

Thread: SQL challange

  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: SQL challange

    Sometimes, as a DBA, you are requested to do some mutations in the database. We all know the nice "old_table" feature so we can LOG & PROVE exactly what we did. But I want to add additional information to that I can show where & when I mutated the database.

    That "old_table" seems very limited. It does not like to joined with other tables...
    The following SQL does not compile on DB2 but it is an indications of what I want.
    Code:
    select    a.current_server as databeest
          ,   a.current_timestamp as tijd
          ,   b.*
    from sysibm.sysdummy1 a
    join  table
    (
           select * from old_table 
              (
                  delete from tjest where id in (2,4)
              )
    ) b
    Anyone who can do some SQL magic to accomplish this?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error messages did you got?

    It might be not "SQL challange" by looking into your code.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dr_te_z View Post
    Anyone who can do some SQL magic to accomplish this?
    Don't know if it accounts to magic, but try something like
    Code:
    select t.*, current timestamp, current server from old table (
      delete from tjest where id in (2,4)
    ) t
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    Don't know if it accounts to magic, but try something like
    Code:
    select t.*, current timestamp, current server from old table (
      delete from tjest where id in (2,4)
    ) t
    Great! Thanks. I was confusing myself with the underscore between "old" and "table". Your SQL works
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by dr_te_z View Post
    ... I was confusing myself with the underscore between "old" and "table". ...
    And you don't need to join with sysibm.sysdummy1.
    But, if you joined, you should specify ON clause, or you should use CROSS JOIN.

    The table sysibm.sysdummy1 has no current_server column nor current_timestamp column. So, you should remove qualifier "a." from them.

    So, your query might be like this...
    Code:
    select    current_server as databeest
          ,   current_timestamp as tijd
          ,   b.*
    from sysibm.sysdummy1 a
    join  table
    (
           select * from old table 
              (
                  delete from tjest where id in (2,4)
              )
    ) b
     ON 0=0
    Oops, another basic problem may be "A data-change-table-reference in a nested-table-expression, i.e. (select ...)" is not allowed.

    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
    Data change table references

    ...
    ... A data-change-table-reference can be specified as the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement, a SELECT INTO statement, or a common table expression.
    ...
    Then, concluded to the n_i's example...
    Code:
    select t.*, current timestamp, current server from old table (
      delete from tjest where id in (2,4)
    ) t

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Thanks guys. Always scary to modify a production database based upon a problem or change-request. Now I can document what/when/which rows I touched on which database.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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