Help with T-SQL and UNIONs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with T-SQL and UNIONs

Author

Topic

RLatham
Starting Member

2 Posts

Posted - 2005-11-14 : 12:31:43

I am writing a stored procedure but my sql statement used in the crystal report uses a UNION to join the tables. The issue is that I need the date field in two different tables and date field is named two different things. So I used a UNION to join them together and order by date. However, when I apply this to my stored procedure used in my ERP software and crystal, it doesn't pick up on my variables.

The code is as follows:
CREATE PROCEDURE dbo.Rpt_PTAccountsReceivableSummary (

@BegDate DATETIME = NULL,
@EndDate DATETIME = NULL

)
AS

BEGIN TRANSACTION
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT arinv.amount, arinv.inv_date, arpmt.recpt_date, arpmt.dom_check_amt
FROM arinv, arpmt with (nolock)
WHERE arinv.inv_date = arpmt.recpt_date
AND arinv.inv_date >= @BegDate
AND arinv.inv_date <= @EndDate

COMMIT TRANSACTION
GO

I am bringing the report up by a range of dates (Beg and End Date) on a form and I am selecting the amount/dom_check_amount to be shown on the report in that range of dates selected. If you can help with getting this code correct, mainly the select statement to bring up only the range of dates desired, that would be great. Thanks!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-14 : 12:43:05

I don't see any UNION in your code, and I would strongly advise against putting any SQL in your Crystal Report. Create your SQL as a view or server-side stored procedure and then just call the dataset directly from your Crystal Report.
Why? 'Cause Crystal sucks, that's why, and then less logic you put into it the better.

RLatham
Starting Member

2 Posts

Posted - 2005-11-14 : 12:52:55

Thanks for getting back to me.

I have the UNION in Crystal directly. It looks like this:

Select Distinct inv_date as Date from arinv
union
Select Distinct recpt_date as Date from arpmt

In the ERP system, you can choose a range of dates. This is connected to the stored procedure to bring up the report. So my stored procedure is somehow not correct in the select statement because when I run the report from the ERP software for let's say, the month of October, it brings up every date in there. Do you have any suggestions to fix this?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-14 : 13:28:03

Use the profiler to see the call to the sp then run that call in query analyser.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources