Crystal 9 Multiple Select statements in Stored Procedure 1

[JohnnyLong]

I have been asked to produce reports to show when a certain date has expired. Instead of having 5 separate reports we would like to have just 1. We want to use 1 stored procedure like this

CREATE PROCEDURE sp_CertificateReports
AS
/***A8 Returned By***/
SELECT Surname AS SurnameA8, FirstName AS FirstNameA8, A8ReturnedDate
FROM rc_Candidates
WHERE A8Returned <= DATEADD(day,30,CURRENT_TIMESTAMP)
ORDER BY A8Returned ASC

/***PLI Expiry Dates***/
SELECT Surname AS SurnamePLI, FirstName AS FirstNamePLI, PLIExpiryDate
FROM rc_Candidates
WHERE PLIExpiry <= DATEADD(day,30,CURRENT_TIMESTAMP)
ORDER BY PLIExpiry ASC

RETURN

If I select the stored procedure in Crystal's database expert and then in the report design choose Database Fields from the Field Explorer, I am only given fields from the first Select statement. Is there any way to see the other fields so I can drag them onto my report?
Hope this is clear.

 

[vidru]

If you're only running the report for one type of date at a time, set up a parameter in the procedure to get accept the type, and use IF statements in the procedure to pull the data for the selected type. You'd need to alias the different date columns so the naming is consistent (for Crystal's sake). Like this:
[tt]
CREATE PROCEDURE sp_CertificateReports
@Param VARCHAR(15)
AS
IF (@Param = 'A8ReturnedDate')
/***A8 Returned By***/
BEGIN
SELECT Surname, FirstName, TheDate = A8ReturnedDate
FROM rc_Candidates
WHERE A8Returned <= DATEADD(day,30,CURRENT_TIMESTAMP)
ORDER BY A8Returned ASC
END

IF (@Param = 'PLIExpiryDate')
/***A8 Returned By***/
BEGIN
SELECT Surname, FirstName, TheDate = PLIExpiryDate
FROM rc_Candidates
WHERE PLIExpiry <= DATEADD(day,30,CURRENT_TIMESTAMP)
ORDER BY PLIExpiry ASC
END
[/tt]
-dave

 

[JohnnyLong]

Thanks for the help Dave. I really need to have one menu button on the mdi which will show the results for all five dates. So instead of having 5 buttons and 5 reports or 1 button and 5 reports, I'd have 1 button and 1 report showing 5 results. Your solution is the next best thing but the user will still have 5 seperate reports to print off. I'm sure Crystal can handle this, surely??!!

John.

 

[vidru]

OK, then I'd suggest using a UNION query in the procedure to get each of the 5 result sets returned as one, then group on the 'Source' of each result:
[tt]
SELECT Source = 'A8ReturntedDate', Surname, FirstName,
TheDate = A8ReturnedDate
FROM rc_Candidates
WHERE A8Returned <= DATEADD(day,30,CURRENT_TIMESTAMP)
UNION
SELECT Source = 'PLIExpiryDate', Surname, FirstName,
TheDate = PLIExpiryDate
FROM rc_Candidates
WHERE PLIExpiry <= DATEADD(day,30,CURRENT_TIMESTAMP)
[/tt]
-dave

 

[JohnnyLong]

Thanks alot Dave, that's just what I needed.

John