February 28, 2002 at 7:28 am
We're starting the design work on a an application that will access two databases - a live database and an archive database. The archive database contains exactly the same structure as the live db EXCEPT it has the addition of a dateperiod id for each column. On a daily basis the live database will be copied to the warehouse including the current timestamp.
The problem... We'll be using Crystal reports and I'd like to use the same report to access both the live and warehouse databases. We decided that all reports should use stored procedures. One way to tackle this would be as follows:
spGetReportData ( @database varchar(10), @reportingperiod int)
IF @database = 'LIVE' BEGIN
SELECT col1, col2 FROM LIVE..table1
ELSEIF @database = 'WAREHOUSE'
SELECT col1, col2 FROM WAREHOUSE..table1
WHERE periodid = @reportingperiod
END
>>
This would effectively mean that we need to write 2 select statements depending on whether we are accessing either the live or warehouse dbs. We could go ahead and build a dynamic piece of SQL that concatenates the db name on the fly but then land up having all our stored procs compiling on the fly which is not ideal.
Are there any better ways of doing this?
February 28, 2002 at 9:15 am
How about using a distributed view? And probably adding the report period to the live db.
Andy
February 28, 2002 at 10:51 am
hi sorry I don't quite follow - I thought distributed views were only used when you wanted to do a union type select across multiple databases/servers. How exactly do you envisage this scenario?
thnks
February 28, 2002 at 11:06 am
I think the trick here is to report off of one view, not do conditional branching to figure out where to get the data from. By using a view to union the table from live + archive you're getting SQL to do the condition work for you. For it to work the structures have to match (so that the union works). So not only does it simplify the logic, you can include "live" data in a report that also needs to include archived data.
Andy
February 28, 2002 at 12:26 pm
Just a vote for Andy's solution.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply