Merge tables
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15277
Printed Date: 03 May 2024 at 7:48pm
Topic: Merge tables
Posted By: mjanssen
Subject: Merge tables
Date Posted: 03 Jan 2012 at 3:54am
Hello,
We use Crystal Reports to generate a report from our monitoring tool and because the year has ended, it is that time again.
We already have some reports that worked great, but we just discovered that our monitoring tool decided to create new tables to store its data in. (database runs on MySQL by the way)
Until now, we had just one table, names statsdata_daily that contained all data we needed. But when we checked yesterday, we had 3 tables:
statsdata_daily (contains data until 28-05-2011)
statsdata_daily_2011_05_29_12 (contains data from 29-05-2011 until 01-11-2011)
statsdata_daily_2011_11_02_3 (contains data from 02-11-2011 until now)
We would like to combine the data in these tables, so we can use one report to generate stats from.
We really have no idea how to do this, but I recon it is possible, but HOW.
Hopefully there is someone out there that can help us with this.
If more information is needed, feel free to ask.
With kind regards,
Martijn Janssen
|
Replies:
Posted By: lockwelle
Date Posted: 03 Jan 2012 at 4:46am
|
I would think that you could use outer joins to link all the tables together. I would assume that the structure is the same, just that they have been split up. If you outer join on the date, you would think that it would work... Of course my usual suggestion is to use stored procedures as this is a) easier to check by using the querying mechanism and b) will give you just 1 table to use instead of 3 as CR would do, and you would probably have to figure out a formula to display the correct field from the correct table for all columns...I am just unfamiliar with stored procs for MySQL (though I hear it is possible now). HTH
|
Posted By: DBlank
Date Posted: 03 Jan 2012 at 5:02am
|
or myabe UNION all of the tables together in a view or a stored proc and then use that as the report source?
|
Posted By: lockwelle
Date Posted: 03 Jan 2012 at 5:12am
|
should have thought of the UNION. You could do it in a command object as well if you don't have the ability for adding views / stored procs
|
Posted By: mjanssen
Date Posted: 03 Jan 2012 at 5:18am
Thanks for your answers. I'm not that familiar with CR yet, so can you explain how to create a UNION ?
|
Posted By: lockwelle
Date Posted: 03 Jan 2012 at 5:21am
|
if you are using a command object, it is just like sql select x,y,z from table 1 union [all] select x,y,z from table 2 command objects are created where you select the tables in your database, just you select a new command object
|
Posted By: mjanssen
Date Posted: 03 Jan 2012 at 11:33pm
Originally posted by lockwelle
if you are using a command object, it is just like sql select x,y,z from table 1 union [all] select x,y,z from table 2 command objects are created where you select the tables in your database, just you select a new command object
And when I want to merge three tables, will the command then be:
select x,y,z from table 1 union [all] select x,y,z from table 2 union [all]
select x,y,z from table 3
|
Posted By: lockwelle
Date Posted: 04 Jan 2012 at 3:11am
|
yes... you can use Union (which will remove duplicates) or Union ALL (which doesn't) just clarifying also as a note, the number of columns and types have to be the same across all of the tables. I am not sure if it is required, but I tend to have the same names in each select as well.
|
Posted By: mjanssen
Date Posted: 04 Jan 2012 at 11:02pm
Yes!
It seems to work
But, unfortunately there is a but...
In all three tables there is a unix time-stamp. In the separate tables,
this was converted to a readable date/time with
"from_unixtime(`statsdata_daily`.`TTIME`/1000)"
But all of a sudden, even though I renamed the command object to the same name as the original table (statsdata_daily) the SQL expression fails with the error (translated from dutch to english):
Error in compiling a SQL-expression:
Error in databaseconnector: 42S02:[MySQL][ODBC 5.1 Driver\[mysqld-5.0.46-enterprise-nt] Table' opmanagerdb.command' doesn't exist [suppliercode database: 1146]
I think this is because the SQL-Expressionfields tree is gone, where it was available in the original report.
Any thoughts?
Edit: I just noticed that the "Create New SQL Expression" in Formula Workshop is greyed out. Guess that is the reason why its gone... But I don't know why though...
|
Posted By: lockwelle
Date Posted: 05 Jan 2012 at 3:05am
|
I don't know. Could it be that CR is confused as the command object has the same name as a table? I've renamed command objects, but never to have the same name as a table. Sorry, I haven't come across this issue before.
|
Posted By: mjanssen
Date Posted: 05 Jan 2012 at 3:09am
Originally posted by lockwelle
I don't know. Could it be that CR is confused as the command object has the same name as a table? I've renamed command objects, but never to have the same name as a table. Sorry, I haven't come across this issue before.
That was also my idea, so I named the command object differently so there was no table with the same name, but that didn't make any difference unfortunately.
|
|