We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Crystal Reports Question

rustypoot
rustypoot asked
on
Medium Priority
303 Views
Last Modified: 2012-05-11
I am working on a report that uses SQL Server 2005 stored procedure. One of the columns in the stored proc is string 30 . But when this field comes in Crystal Reports, it is coming over as String 1. I am baffled and donot know where and how it is getting changed! Your help will be much appreciated. Here is the SQL Code. The field is TaskStatusCode.

ALTER PROCEDURE [dbo].[hsp_eMARDowntime_DailyTasks](  

@ClientGUIDVar varchar(16),
@OrderGUIDVar varchar(16),
@OrderTaskGUIDVar varchar(16),
@DateOffset int
)  
AS  

SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
@StartDate datetime,
@StopDate datetime,
@CurrentDate datetime

DECLARE
@ClientGUID numeric(16,0),
@OrderGUID numeric(16,0),
@OrderTaskGUID numeric(16,0)

SELECT
@ClientGUID = cast(@ClientGUIDVar as numeric(16,0)),
@OrderGUID = cast(@OrderGUIDVar as numeric(16,0)),
@OrderTaskGUID = cast(@OrderTaskGUIDVar as numeric(16,0))


SELECT @CurrentDate = getdate()
SELECT @StartDate = dateadd(dd, @DateOffset, CAST(CONVERT(varchar(10), @CurrentDate, 101) as DateTime))
SELECT @StopDate = dateadd(ms, -3, dateadd(dd, 1, @StartDate))

SELECT
CASE       WHEN oto.TaskStatusCode = 'Performed' THEN PerformedFromDtm
      ELSE ScheduledDtm -- Overdue, Pending,
END as OccurrenceDtm,
oto.TaskStatusCode,
ts.code,
@StartDate as StartDate
into #x
FROM
CV3OrderTaskOccurrence oto,
CV3TaskStatus ts
WHERE
oto.TaskStatusCode = ts.code
AND oto.ClientGUID = @ClientGUID
AND oto.OrderGUID = @OrderGUID
AND oto.OrderTaskGUID  = @OrderTaskGUID
AND oto.TaskStatusCode = ts.code
AND ScheduledDtm between @StartDate and @StopDate

IF @@RowCount = 0  
BEGIN
SELECT
cast(null as datetime) as OccurrenceDtm,
'' as TaskStatusCode,
'' as TaskCode,
@StartDate as StartDate
END
ELSE
SELECT * from #x






Comment
Watch Question

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks so much!!!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.