Avatar of Stacie
Stacie
Flag for United States of America asked on

Error on script: Conversion failed when converting the nvarchar value 'CTAUTHNONCHA' to data type int.

I 'm getting the following error when I'm changing this line of code

(453 row(s) affected)
Msg 245, Level 16, State 1, Line 45
Conversion failed when converting the nvarchar value 'CTAUTHNONCHA' to data type int.


From: WHERE PS_PROD.PRODDTA.F31122.WTDGL between @BeginDate and @EndDate
AND IWMCUW IN ('         101')--,'         110')--,'         111')--,'         113')

To:
WHERE PS_PROD.PRODDTA.F31122.WTDGL between @BeginDate and @EndDate
AND IWMCUW IN ('         110')--,'         110')--,'         111')--,'         113')

Here is the entire code line

-- [dbo].[Acct_RateCalculations]

DROP TABLE #TEMP_FILE#;
DROP TABLE #TEMP_O_ID;
DROP TABLE #DetailHours;



DECLARE
 @Start_Date smalldatetime = '01/01/14'
,@End_Date as smalldatetime = '10/31/14'
,@Business_Unit as varchar(3) ='101' --'101' '103' '104' '106' '107' '110' '111'
,@BeginDate INT
,@EndDate INT
,@EndDateHRS DATETIME
,@Business_Unit2 VARCHAR(12)

--DECLARE @DetailHours TABLE
CREATE TABLE #DetailHours
		(
			 BusinessUnit	INT
			,[Hours]		DECIMAL(11,2)
			,JobTicket		INT
			,SoldTo			INT
		)

SET @BeginDate = (select e1_integrate.dbo.ConvertToPeopleSoftDate(@Start_Date));
SET @EndDate = (select e1_integrate.dbo.ConvertToPeopleSoftDate(@End_Date));
SET @Business_Unit2 = (SELECT '         '+@Business_Unit)

SELECT DISTINCT file#
INTO #TEMP_FILE#
FROM Acct_RR_ADP_WData
WHERE [DATE] BETWEEN @Start_Date AND @End_Date
AND category = 5511 

SELECT DISTINCT O_ID 
INTO #TEMP_O_ID
FROM Acct_RR_ADP_ID_CrossRef
WHERE ADPnum IN (select file# from #TEMP_FILE#);


INSERT INTO #DetailHours(BusinessUnit,[Hours],JobTicket)
Select 
IWMCUW
--,WTMCU
,SUM(WTHRW)/100.00 AS 'HOURS'
--,WTAN8
--,WTTYR
--,WTDOCO
,RTRIM(SUBSTRING(WTKITL,CHARINDEX('-',WTKITL)+1,25)) AS 'JobTicket'
--,WTDGL
FROM PS_PROD.PRODDTA.F31122 WITH (NOLOCK)
INNER JOIN PS_PROD.PRODDTA.F30006 WITH (NOLOCK) on WTMCU = IWMCU AND WTMMCU = IWMMCU
WHERE PS_PROD.PRODDTA.F31122.WTDGL between @BeginDate and @EndDate
AND IWMCUW IN ('         101')--,'         110')--,'         111')--,'         113')

AND WTMMCU = '         108'
AND WTTYR = '1'
AND NOT WTHRW = 0
AND WTAN8 IN (SELECT O_ID FROM #TEMP_O_ID)
GROUP BY IWMCUW,RTRIM(SUBSTRING(WTKITL,CHARINDEX('-',WTKITL)+1,25));

INSERT INTO #DetailHours(BusinessUnit,[Hours],JobTicket)
Select 
IWMCUW
--,WTMCU
,SUM(WTHRW)/100.00 AS 'HOURS'
--,WTAN8
--,WTTYR
--,WTDOCO
,RTRIM(SUBSTRING(WTKITL,CHARINDEX('-',WTKITL)+1,25)) AS 'JobTicket'
--,WTDGL
FROM PS_PROD.PRODDTA.F31122 WITH (NOLOCK)
INNER JOIN PS_PROD.PRODDTA.F30006 WITH (NOLOCK) on WTMCU = IWMCU AND WTMMCU = IWMMCU
WHERE PS_PROD.PRODDTA.F31122.WTDGL between @BeginDate and @EndDate
AND IWMCUW IN ('         103','         104')
AND WTMMCU = '         108'
AND WTTYR IN ('2','1')
AND NOT WTHRW = 0
AND WTAN8 IN (SELECT O_ID FROM #TEMP_O_ID)
GROUP BY IWMCUW,RTRIM(SUBSTRING(WTKITL,CHARINDEX('-',WTKITL)+1,25));







WITH cteAddressF4211 AS 
	(
		SELECT DISTINCT SDAN8,CAST(SDURAB AS INT) AS 'SDURAB'
		FROM F4211
		WHERE SDURAB IN (SELECT JobTicket FROM #DetailHours)
		AND SDURCD = 'JT'
	)
UPDATE #DetailHours
SET SoldTo = SDAN8
FROM cteAddressF4211
WHERE SDURAB = JobTicket;


WITH cteAddressF42119 AS 
	(
		SELECT DISTINCT SDAN8,CAST(SDURAB AS INT) AS 'SDURAB'
		FROM F42119
		WHERE SDURAB IN (SELECT JobTicket FROM #DetailHours WHERE SoldTo IS NULL)
		AND SDURCD = 'JT'
	)
UPDATE #DetailHours
SET SoldTo = SDAN8
FROM cteAddressF42119
WHERE SDURAB = JobTicket;


SELECT * FROM #DetailHours


DROP TABLE #TEMP_FILE#;
DROP TABLE #TEMP_O_ID;
DROP TABLE #DetailHours;

/*
-- REMOVE UNWANTED HOUR TYPES
IF @Business_Unit2 IN ('         103','         104')
	BEGIN
		DELETE FROM #TEMP_WC_Hours_Details1
		WHERE NOT WTTYR IN ('1','2')
	END
ELSE 
	BEGIN 
		DELETE FROM #TEMP_WC_Hours_Details1
		WHERE NOT WTTYR = '1'
	END


-- Calculates Detail Hours for the pressroom 106
Select 
 WTMCU
,WTHRW
,WTTYR
INTO #TEMP_WC_Hours_106D
FROM F31122 WITH (NOLOCK)
INNER JOIN F30006 WITH (NOLOCK) ON WTMCU = IWMCU AND WTMMCU = IWMMCU
WHERE F31122.WTDGL Between  @BeginDate and @EndDate
and IWMCUW = '         106' 
and WTTYR in (2,3,4)
AND NOT WTHRW = 0



-- CALCULATES DISTRIBUTION HOURS 107

SELECT 
 WTDOCO
,WTTYR
,WTMCU
,SUM(WTHRW) AS 'WTHRW'
--INTO #TEMP_DIST_HRS_107D		-- 140925 CHANGE
FROM F31122 WITH (NOLOCK)
WHERE WTDGL Between  @BeginDate and @EndDate
AND WTTYR in (2,3)
AND WTMCU IN ('    SHANKLIN','    HANAGATA','    HANDWORK')
GROUP BY WTMCU,WTDOCO,WTTYR

CONFIRM HOUR TYPE FOR 110 AND 111, AND 113.




*/

Open in new window

Microsoft SQL Server 2005Microsoft SQL Server 2008MySQL Server

Avatar of undefined
Last Comment
Stacie

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Stacie

ASKER
the error is in this line.

INSERT INTO #DetailHours(BusinessUnit,[Hours],JobTicket)
Select
IWMCUW
Stacie

ASKER
Thank you Jim. I'm very new to TSQl

So where exactly I should put this line of code?

 common way to deal with this is to add ... WHERE ISNUMERIC(column_name) = 1 to the end of the INSERT, which will only affect the int format rows.  Then create another select with ... WHERE ISNUMERIC(column_name) = 0
Jim Horn

INSERT INTO #DetailHours(BusinessUnit,[Hours],JobTicket)
Select  IWMCUW

Find out for us what the data types are for BusinessUnit and IWMCUW.

As an aside, it is not best practice to store numbers in char columns.

>Thank you Jim. I'm very new to TSQl
>So where exactly I should put this line of code?
Add it to the WHERE clause.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Stacie

ASKER
BusinessUnit is a INT
IWMCUW is a String
Jim Horn

Yeah there's the problem.  If somebody types 'banana' into the IWMCUW column, it is accepted becuase it is a string, even though it should be a number.  Then the above code tries to slam 'banana' into an int column, and throws the error.

Do this real quick, and the resulting values are what you'll have to gracefully handle, either by ignoring them in your code, or getting someone (you?) to change these values to a valid int
Select  IWMCUW
FROM PS_PROD.PRODDTA.F31122 -- or PS_PROD.PRODDTA.F30006, whatever table this resides in
WHERE ISNUMERIC(IWMCUW) = 0

Open in new window

Stacie

ASKER
Thank you Jim I understand the concept in all that just not sure how to modify the script.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Horn

If by 'modify the script' you mean add the ISNUMERIC filter, then in the statements above where there is a WHERE and a lot of AND's, add this one...

   AND ISNUMERIC(IWMCUW) = 0

Open in new window


You'll still have the problem where the 'banana' rows weren't a part of that query, and having to replace the 'banana' values with some int value.
Stacie

ASKER
ah I got you no luck tks you for the help