Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Searching many tables for a change in value

Searching many tables for a change in value

Searching many tables for a change in value

Hi SQL Experts,

   We have a situation where we have a sales order table with a Sales Order say number 000999 which has attached to it a serial number 2345678.  At some point in the life of the sales order, this serial number could change to a different number, e.g. 7891011, because the customer upgraded.  Currently, we have no way of tracking when this happened!  Our IT group is working on that... but in the meantime, what we have is a 'live' sales order table which would currently show:

Sales_Order_Number, Serial_No
000999    7891011

We also have frozen tables for each month in the exact same format. So some month since Jan 2007,(transaction could have only happened as of 1/1/2007) we could find in one of the 20 frozen month tables, say Sales_Orders_2007_JUN_Frozen:

Sales_Order_Number, Serial_No
000999    2345678

Someone asked me if I could search all the tables to get a count of how many times this has happened.  Even better yet, identify the sales order and the month it was changed.

     Is there any way for me to search those 20 separate tables (2007_JAN...2008_AUG) all at once for each Sales_Order (they are estimating 20,000 such orders)? I'm using Hyperion Interactive, but could use anything.  There are an avg of 4 million rows in the Sales_order table and it has 40 columns.

It sounds like maybe a script is in my future...?

Thank you!

RE: Searching many tables for a change in value

Be aware that that PHVs solution may not be speedy with so many rows, so I suggest you run it when the server is least busy.

"NOTHING is more important in a database than integrity." ESquared

RE: Searching many tables for a change in value

Did the trick, thanks PHV!  And yes, very slow -thanks sQLSister for the warning.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close