Reporting off stored procedure with SELECT UNION

[tammiew]

I am using Crystal Reports in Visual Studio .NET. I have created a report which reports off of a stored procedure. I was able to do the report just fine when the stored procedure had one SELECT statement. The thing is, there are two tables which contain the same field names (I understand how to set up an alias so the column headings are good): one is an archive table, one is current. For this report, I have to look at all the data in both tables, pulling data which matches my criteria (InDateTime >= @BeginDate and InDateTime <= @EndDate. I did a UNION and now when I run the stored procedure, it's as if Crystal Reports doesn't recognize the UNION - it reports the data from the first SELECT. Does anyone know if you can report off of a stored procedure which contains a UNION?

Thanks in advance,
tammiew

 

[synapsevampire]

I try not to use Unions, but I just tested against a SQL Server 2000 SP and CR 8.5 had no problem with a Union.

I'd guess that your pointing at the wrong SP (perhaps you have multiple versions, or one is saved under a different user), or the SP isn't functioning as expected.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

You may want a Union ALL, not just a Union if there's duplicated data that you require

I try not to use Unions, but I just tested against a SQL Server 2000 SP containing a Union using CR 8.5 and had no problems getting the correct results.

You may be pointing at the wrong SP (perhaps you have multiple versions, or one is saved under a different user) or the SP isn't functioning as expected.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

Yes you can report off a stored procedure that uses a union. You shouldn't have a problem with column names, the union uses the column names from the first half of the union as the column names for the output. I suspect that there is a problem with your SP, not crystal. Try running your SP outside crystal, you will probably get the same result.

Lisa

 

[tammiew]

Thanks to both of you. I'll go check - I must be pointing to the wrong one. The SP ran fine up until I did some editing and added the UNION so I bet you're right on the money. I'm behind on the project and definitely not thinking too good at this point (lack of sleep perhaps?).

Kai, what do you have against the use of a UNION? Just curious. I'm still new at some of this and always interested in hearing folk's opinions.

Thanks again,
Tammie