| Author |
Message |
mjanssen
Newbie
Joined: 03 Jan 2012
Location: Netherlands
Online Status: Offline
Posts: 5
|
   Topic: Merge tables
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
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
Edited by DBlank - 03 Jan 2012 at 5:02am
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
mjanssen
Newbie
Joined: 03 Jan 2012
Location: Netherlands
Online Status: Offline
Posts: 5
|
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 ?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
mjanssen
Newbie
Joined: 03 Jan 2012
Location: Netherlands
Online Status: Offline
Posts: 5
|
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
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
Edited by lockwelle - 04 Jan 2012 at 3:12am
|
|
IP Logged |
|
mjanssen
Newbie
Joined: 03 Jan 2012
Location: Netherlands
Online Status: Offline
Posts: 5
|
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...
Edited by mjanssen - 04 Jan 2012 at 11:10pm
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
|
|
IP Logged |
|
|
|
