Merging two tables
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1714
Printed Date: 02 May 2024 at 12:53pm
Topic: Merging two tables
Posted By: Rob-VDMChem
Subject: Merging two tables
Date Posted: 19 Nov 2007 at 11:42am
| Is it possible to have the data from 2 subreports show up together and not separately? Allow me to clarify, I have a purchase order report I do on a monthly basis. Some PO's are OPEN (PO table) and some are CLOSED (POHIST table). I use the LastFullMonth filter on the orderdate field for showing the last full month. What I was wondering is can I have the data from the 2 different data sources displayed all together in order by PO Number rather than one report displaying first and the other at the end. Hope this makes sense. TIA!! |
Replies:
Posted By: BrianBischof
Date Posted: 19 Nov 2007 at 12:58pm
|
You have to use a SQL Command object and use the UNION keyword to join the data. Create two select statements that return the same fields and test them. Once they are working, put the UNION keyword between them to join the data into a single virtual table. That allows you to sort and filter the data as if it were one table. I cover the UNION keyword and lots more database tricks in Chapter 11, "Maximizing SQL Performance", of my new CR Encyclopedia book. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Rob-VDMChem
Date Posted: 19 Nov 2007 at 1:53pm
| Thanks. You are the man! |
Posted By: Rob-VDMChem
Date Posted: 20 Nov 2007 at 6:33am
|
Ok Brian I have a new issue. I tried to create a simple SELECT command in the SQL expression editor, SELECT "PO"."ORDERDATE" FROM "MP2PROD"."dbo"."PO" "PO" - MP2PROD being the ODBC database name) It keeps coming back saying "incorrect syntax near keyword SELECT" I've tried it with and without the WHERE ("PO"."ORDERDATE">={ts '2007-10-01 00:00:00'} AND "PO"."ORDERDATE"<{ts '2007-11-01 00:00:00'}) Any suggestions!!! PS I'm ordering your book today!!! |
Posted By: Lugh
Date Posted: 20 Nov 2007 at 7:04am
|
I'll admit that I've never really used the SQL Expression bit before.
But, when I look at it, it seems to be using single quotes around the field names, not double
quotes. Could that be your issue? What kind of database are you running this against? |
Posted By: Rob-VDMChem
Date Posted: 20 Nov 2007 at 7:13am
| Thanks for the reply. I have tried it both ways with single and double quotes with the same results. It's an access 2000 database ODBC connection. It is the maintenance software our company uses called MP2. |
Posted By: Rob-VDMChem
Date Posted: 20 Nov 2007 at 8:16am
| I think I found my problem. I was trying to create the command int he expression not the command area. I will try it and let you know. Sorry about that! |
Posted By: Rob-VDMChem
Date Posted: 20 Nov 2007 at 11:36am
|
OK, here is what I have int he SQL COMMAND, SELECT PONUM,ORDERDATE,SUBTOTAL FROM POHISTH UNION SELECT PONUM,ORDERDATE,SUBTOTAL FROM PO Do the common fields need to be linked? |
Posted By: BrianBischof
Date Posted: 20 Nov 2007 at 12:01pm
|
No linking is necessary. What you are doing is creating two distinct lists of data from two different tables. The UNION keyword then appends the second list to the first list into a virtual table and makes it all appear as one single table. Thus, the number of rows in the new table will be equal to how many rows are in the first SELECT plus how many rows are in the second SELECT.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: BrianBischof
Date Posted: 20 Nov 2007 at 12:02pm
Oh yeah - thanks for supporting the forum by buying a book! If more people did this, I could spend more time creating new content for the site. 
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Rob-VDMChem
Date Posted: 20 Nov 2007 at 12:06pm
| OK I got it!!! I was having an issue editing an existing report and it wasn't liking what I was doing. Started from scratch, plugged in the command and BINGO she be working without a hitch!! Thanks for the point in the right direction Brian! |
Posted By: Lugh
Date Posted: 21 Nov 2007 at 5:27am
|
Originally posted by BrianBischof No linking is necessary. What you are doing is creating two distinct lists of data from two different tables. The UNION keyword then appends the second list to the first list into a virtual table and makes it all appear as one single table. Thus, the number of rows in the new table will be equal to how many rows are in the first SELECT plus how many rows are in the second SELECT. As a further note, you do have to make very sure that your columns line up, and that the datatypes in your columns are the same. In the case you're working on here, that is pretty trivial. In some other cases, you may have to some data type conversion and/or column aliasing to make everything line up right. If the columns don't line up, SQL chokes (for, I think, fairly obvious reasons). |
Posted By: Rob-VDMChem
Date Posted: 21 Nov 2007 at 5:39am
| Thanks Lugh, Very good note. Fortunately the field names were the same with the same data types for both tables. The problem I was having was getting CR to not LOOP data over and over from one of the tables. Once I started the report from scratch there were no issues. Thanks for everyones help!! |
Posted By: kbrearey
Date Posted: 08 Sep 2010 at 8:16am
| I know this is several years removed from the initial topic, but I was wondering if there was any way to do exactly this same function with 2 tables from different databases? The reason I ask is because I have a 'Live' database featuring data from between today and 30 days back. All previous data is in an 'Archive' database which resides on a different server (using a different DBMS). Is there any way to accomplish this same task with this situation in mind? |