SQL Union usage help

[ausmoran]

I am attempting to utilize several Select Statements to combine the results into a single dataset, but am not having any luck. I am hoping that someone might be able to lend a hand. I am an absolute beginner with Crystal (version 9) so any assistance you might be able to offer will be most gratefully appreciated.

Here is the SQL that I want to use:
-----------------------------------

select lname, fname, middle, officename, address1, address2, city, state, zip
from tbl_PanelMembers
union
select lname, fname, middle, officename, address1, address2, city, state, zip
from tbl_Litigants
Union
select lname, fname, middle, officename, OfficeStreetaddress1, OfficeStreetaddress2, city, state, zip
from tbl_GrievanceCommittee
Union
select lname, fname, middle, officename, address1, address2, city, state, zip
from tblMediators
Union
select lname, fname, mname, officename, address1, address2, city, state, zip
from tblAttorneys

---------------------------------------
Thanks again for your help,
Austin

 

[synapsevampire]

Rather than say "...but am not having any luck.", why not state what your difficulty is, error messages, etc? Luck doesn't describe anything useful.

To create a UNION dataset from CR 9, use the Add Command under the datasource you're using, and then I'd change the SQL slightly, but you may not need to, you've given no indication what you need to do within the report, you might have been better off posting in the appropriate database forum.

Anyway, try:

select 'tbl_PanelMembers' MySource, lname, fname, middle, officename, address1, address2, city, state, zip
from tbl_PanelMembers
union all
select 'tbl_Litigants' MySource, lname, fname, middle, officename, address1, address2, city, state, zip
from tbl_Litigants
Union all
...etc...

Now you know the source of all of the data in the MySource column, and you'll get all of the rows.

The Union All says add all rows, a Union only adds unique rows.

-k

 

[ausmoran]

Thank you for taking the time to respond to my question, SYNAPSVAMPIRE! You are absolutely correct...I couldn't have been much more vague. Let me provide a little more background to explain what I am attempting to accomplish.

I want to build mailing labels for specific people from five different tables (as shown in the separate select statements above, joined by way of the UNION statement).

I do NOT want to select all records from each table. I want to select only those records pertaining to a specific USERID and CASEID (linked fields to the CASE table and the USER table).

I don't want to have to run five different queries in order to get all the people involved in a case onto a sheet of labels. So I thought I could do it using the UNION statement.

As I said, I am very new to Crystal so am not certain how to accomplish this task. These labels are to hopefully become part of an ASP.NET application if I can figure this out.

Once again, I am very appreciative of any assistance you might be able to provide. I'm hopeful that I may have cleared up some of the mystery.

Austin

 

[synapsevampire]

I think that your original statement should work OK, again, what is failing?

And the UNION ALL doesn't mean you get all rows, but if you want mailing labels, using a Union is better to help to eliminate dupes.

Within the Command object you cna build parameters and insert them into the SQL if you want to pass through any specific WHERE criteria, such as an ID.

You also might consider building a View on the database if you have a multiple value parameter.

Mailing labels are usually best accomplished using the built in Mailing Label wizard.

-k

 

[ausmoran]

Perhaps I don't completely understand how to use the Mailing Label Wizard in a way that will allow me to insert a SQL Query utilizing five separate tables. Let me attetmpt to further explain the steps that I am taking, and the error that is generated:

I have set up the basic label (not using the wizard). In the left column, I can expand the DATABASE FIELDS (in the fields explorer) and see all of the fields from the various tables that I want. I have made the assumption that I need to add the SQL Query in the SQL Expression Fields.

In the SQL Expression Editor, I have tried to add my SQL Statement (as laid out above with the UNIONs) and I get the following error when I try to save it:

Error in Compiling SQL Expression:
Failed to open a Rowset.
Details: ADO Error Code: 0x800040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Only one source can be specified in the select list when the subquery is not introduced with EXISTS.
SQL State: 42000
Native Error: 116

I hope this helps a little. As I said, I am so new to Crystal, I am not sure what else to provide. Thanks for your extreme patience and wonderful assistance. I gratefully appreciate it.

- Austin

 

[Turkbear]

Hi,
No..The Sql Expression is something entirely different ( like SUM({field}) )..

The Command object ( built with your Sql code with the UNIONs) should provide only 1 choice for each field ( the UNION makes it appear to be 1 table only)..

Your Command object does not include the fields you need for restricting the data returned ( CASEID and USERID) so it will not be possible, using it, to limit the records by that criteria..
Add those fields to the Command ( Or create a view in SqlServer that joins the tables using CASEID and USERID ) and use this view instead...You can then apply the selection criteria as needed.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ausmoran]

Great idea Turkbear! I will do that now and simply pull the data using the label wizard. I think even I might be able to manage that! I really appreciate your taking the time to respond and help me out!

Have a good day!

- Austin