asked on

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

ste5an

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.

tanj1035

ASKER

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.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tanj1035

ASKER

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

Vitor Montalvão

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

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

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