SQL, "Conversion failed when converting the varchar value 'Sent' to data type int."

Hi Expert,

I want to open [dbo].[ResponseTimesbyjoy], but it came up the error message "Conversion failed when converting the varchar value 'Sent' to data type int." I do not know who has changed the database. I was trying to  dig out which column contains " sent" based on reviewing the script, and I do not think any column should contain "sent".  My manager suspects that the issue may come from function " dbo.GetResponseTimestamp". I checked its script and did not see anything wrong,  maybe because I do not know how to dig out the issue from the script.
Can  someone help me how to fix [dbo].[ResponseTimesbyjoy] and make it readable?  Thanks.


Table Script

alter VIEW [dbo].[ResponseTimesbyjoy]

AS
 
WITH ResponseTimesCTE

AS (

	SELECT DealResponse.DealId

	,Deal.DealerID

	,Dealer.NAME

	,RANK() OVER (

	PARTITION BY DealResponse.DealId ORDER BY TIMESTAMP ASC

	) AS SequenceNumber

	,[Timestamp]

	,dbo.WorkingMinutesBetweenDates([Timestamp], 

	dbo.GetResponseTimestamp(DealResponseId)) AS ResponseTimeMin,

	CASE 

	WHEN ShopperResponseStatus IN (1,4,5) THEN 'Dealer'

	WHEN DealerResponseStatus IN (1,4,5) THEN 'Shopper'

	END AS Responder

	,CASE [DealStatus]

	WHEN 0 THEN 'Active'

	WHEN 1 THEN 'Expired'

	WHEN 2 THEN 'Accepted'

	WHEN 3 THEN 'Attention'

	WHEN 4 THEN 'Archived'

	WHEN 30 THEN 'Active-Attention'

	WHEN 31 THEN 'Expired-Attention'
	END AS DealStatus
	FROM makemydeal_com..DealResponse2 as DealResponse

	INNER JOIN Deal ON DealResponse.DealId = Deal.DealID

	INNER JOIN Dealer ON Deal.DealerID = Dealer.DealerID

	WHERE dbo.GetResponseTimestamp(DealResponseId) IS NOT NULL

	)
 
SELECT DealId

	,NAME

	,SequenceNumber

	,ResponseTimeMin

	,CASE 

	WHEN DATEPART(WEEKDAY, [Timestamp]) = 1 --Sunday

	OR (DATEPART(HOUR, [Timestamp]) < 9 AND 18 < DATEPART(HOUR, [Timestamp])) --After Hours 9pm - 7am

	THEN 'After Hours'

	WHEN ResponseTimeMin <= 15 THEN '0 - 15 Min'

	WHEN 15 < ResponseTimeMin AND ResponseTimeMin <= 30 THEN '16-30 Min'

	WHEN 30 < ResponseTimeMin AND ResponseTimeMin <= 45 THEN '31-45 Min'

	WHEN 45 < ResponseTimeMin AND ResponseTimeMin <= 60 THEN '46-60 Min'

	WHEN 60 < ResponseTimeMin AND ResponseTimeMin <= 120 THEN '1-2 Hours'

	WHEN 120 < ResponseTimeMin AND ResponseTimeMin <= 720 THEN '2-12 Hours'

	WHEN 720 < ResponseTimeMin AND ResponseTimeMin <= 1440 THEN '12-24 Hours'

	WHEN 1440 < ResponseTimeMin THEN '1 Day +'

	END AS TimeSlice

	,Responder

	,[Timestamp]

	,DealStatus

FROM ResponseTimesCTE

GO

Open in new window

FUNCTION SCRIPT

CREATE FUNCTION [dbo].[GetResponseTimestamp](@DealResponseId INT)

RETURNS DATETIME
AS
BEGIN
DECLARE @DealId INT;
DECLARE @Responder_DealResponseId INT;
SET @DealId = (SELECT DealId 
FROM dbo.DealResponse 
WHERE DealResponseId = @DealResponseId);
--Check if 
IF (SELECT ShopperResponseStatus FROM DealResponse WHERE DealResponseID = @DealResponseId) IN (1,4,5)
SET @Responder_DealResponseId = 
(SELECT MIN(DealResponseId) 
FROM DealResponse 
WHERE DealID = @DealId 
AND DealResponseID >= @DealResponseId 
AND DealerResponseStatus IN (1,4,5));
ELSE 
SET @Responder_DealResponseId =
(SELECT MIN(DealResponseId) 
FROM DealResponse 
WHERE DealID = @DealId 
AND DealResponseID >= @DealResponseId 
AND ShopperResponseStatus IN (1,4,5));
RETURN (SELECT Timestamp 
FROM DealResponse

Open in new window

tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
How are [DealerResponseStatus] and [ShopperResponseStatus] defined? Are they of data type INT?

E.g.
SELECT  DISTINCT
        DealerResponseStatus
FROM    DealResponse
WHERE   ISNUMERIC(DealerResponseStatus) = 0;

SELECT  DISTINCT
        ShopperResponseStatus
FROM    DealResponse
WHERE   ISNUMERIC(ShopperResponseStatus) = 0;

Open in new window


btw, your function is nonsense. It could be reduced to

CREATE FUNCTION [dbo].[GetResponseTimestamp] ( @DealResponseId INT )
RETURNS DATETIME
AS
    BEGIN
        RETURN (SELECT Timestamp  FROM DealResponse );
    END;

Open in new window


Which must mean that [DealResponse] contains only one row. Otherwise the function would return arbitrary values.

p.s. use the toolbar and the embed code button to place code in a more readable fashion into your posts.
use-code-tags-please.png
1
tanj1035Author Commented:
Hi, Thanks for your reply.

the data type of  DealerResponseStatus and ShopperResponseStatus are varchar. Based on your query, it returned the value:
"Accepted
Read
None
Sent
Archived
Unread"

Thanks for  your suggestion on function query and post writing. Looking forward to hearing from you.
0
ste5anSenior DeveloperCommented:
So your predicate DealerResponseStatus IN (1,4,5)); is wrong.

Either there is a status table, then your DealResponse table should reference it or you need to filter for the text values.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanj1035Author Commented:
You are right, "dealerresponsestatus" does not contain int. Now, I  pulled out the data from another table for "dealerresponsestatus " & "shopperresponsestatus" which contain the int, not the varchar (i.e.sent). Please see my query below. It can successfully call the function. But when I alter the view with the revised function, it still return the error message" "Conversion failed when converting the varchar value 'Sent' to data type int."
Do you have any thoughts? Thanks.
alter VIEW [dbo].[ResponseTimesbyjoy2]
AS

WITH ResponseTimesCTE
AS (
	SELECT d2.DealId
		,d.DealerID
		,dr.NAME
		,RANK() OVER (
			PARTITION BY d.DealId ORDER BY TIMESTAMP ASC
			) AS SequenceNumber
		,[Timestamp]
		,dbo.WorkingMinutesBetweenDates([Timestamp], dbo.GetResponseTimestampbyjoy2(DealResponseId)) AS ResponseTimeMin
		,CASE 
			WHEN d2.ShopperResponseStatus IN (1,4,5) THEN 'Dealer'
			WHEN d2.DealerResponseStatus IN (1,4,5) THEN 'Shopper'
		END AS Responder
		,CASE [DealStatus]
			WHEN 0 THEN 'Active'
			WHEN 1 THEN 'Expired'
			WHEN 2 THEN 'Accepted'
			WHEN 3 THEN 'Attention'
			WHEN 4 THEN 'Archived'
			WHEN 30 THEN 'Active-Attention'
			WHEN 31 THEN 'Expired-Attention'
		END AS DealStatus
	
	FROM makemydeal_com..DealResponse2 as D2
	INNER JOIN mmd_feed..deal d ON d2.DealId = d.DealID
	INNER JOIN mmd_feed..dealer dr ON dr.DealerID = d.DealerID
	--WHERE dbo.GetResponseTimestampbyjoy2(DealResponseId) IS NOT NULL
	)



SELECT DealId
	,NAME
	,SequenceNumber
	,ResponseTimeMin
	,CASE 
		WHEN DATEPART(WEEKDAY, [Timestamp]) = 1 --Sunday
			OR (DATEPART(HOUR, [Timestamp]) < 9 AND 18 < DATEPART(HOUR, [Timestamp])) --After Hours 9pm - 7am
			THEN 'After Hours'
		WHEN ResponseTimeMin <= 15 THEN '0 - 15 Min'
		WHEN 15 < ResponseTimeMin AND ResponseTimeMin <= 30 THEN '16-30 Min'
		WHEN 30 < ResponseTimeMin AND ResponseTimeMin <= 45 THEN '31-45 Min'
		WHEN 45 < ResponseTimeMin AND ResponseTimeMin <= 60 THEN '46-60 Min'
		WHEN 60 < ResponseTimeMin AND ResponseTimeMin <= 120 THEN '1-2 Hours'
		WHEN 120 < ResponseTimeMin AND ResponseTimeMin <= 720 THEN '2-12 Hours'
		WHEN 720 < ResponseTimeMin AND ResponseTimeMin <= 1440 THEN '12-24 Hours'
		WHEN 1440 < ResponseTimeMin THEN '1 Day +'
		END AS TimeSlice
	,Responder
	,[Timestamp]
	,DealStatus
FROM ResponseTimesCTE

Open in new window



use mmd_feed
GO

/****** Object:  UserDefinedFunction [dbo].[GetResponseTimestampbyjoy2]    Script Date: 6/25/2015 1:41:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO





alter FUNCTION [dbo].[GetResponseTimestampbyjoy2](@DealResponseId INT)
RETURNS DATETIME
AS
BEGIN

DECLARE @DealId INT;
DECLARE @Responder_DealResponseId INT;

SET @DealId = (SELECT DealId 
				FROM makemydeal_com..dealresponse2 
				WHERE DealResponseId = @DealResponseId);

--Check if 
IF (SELECT ShopperResponseStatus FROM DealResponse WHERE DealResponseID = @DealResponseId) IN (1,4,5)
	SET @Responder_DealResponseId = 
		(SELECT MIN(DealResponseId) 
		FROM makemydeal_com..dealresponse2 
		WHERE DealID = @DealId 
			AND DealResponseID >= @DealResponseId 
			AND DealerResponseStatus IN (1,4,5));

ELSE 
	SET @Responder_DealResponseId =
		(SELECT MIN(DealResponseId) 
		FROM makemydeal_com..dealresponse2 
		WHERE DealID = @DealId 
			AND DealResponseID >= @DealResponseId 
			AND ShopperResponseStatus IN (1,4,5));


RETURN (SELECT Timestamp 
		FROM makemydeal_com..dealresponse2 
		WHERE DealResponseID = @Responder_DealResponseId);

END




GO

select [dbo].[GetResponseTimestampbyjoy2] (914)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you change your function as ste5an recommended?
Try to add an ELSE statement in the CASEs:
CASE 
	WHEN d2.ShopperResponseStatus IN (1,4,5) THEN 'Dealer'
	WHEN d2.DealerResponseStatus IN (1,4,5) THEN 'Shopper'
     ELSE NULL
END AS Responder
,CASE [DealStatus]
	WHEN 0 THEN 'Active'
	WHEN 1 THEN 'Expired'
	WHEN 2 THEN 'Accepted'
	WHEN 3 THEN 'Attention'
	WHEN 4 THEN 'Archived'
	WHEN 30 THEN 'Active-Attention'
	WHEN 31 THEN 'Expired-Attention'
         ELSE NULL
END AS DealStatus

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.