Solved

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

Posted on 2014-11-10
9
276 Views
Last Modified: 2015-03-30
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

0
Comment
Question by:yguyon28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40432980
Execute it again in SSMS, double-click on the error message and the cursor will jump to the offending line.
Then tell us where in the code block above that line is.

The likely cause is an INSERT somewhere that is inserting a char column into an int column, and one of the values isn't convertable to int.  A 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, and gracefully handle all the non-int's.
0
 

Author Comment

by:yguyon28
ID: 40432981
the error is in this line.

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

Author Comment

by:yguyon28
ID: 40432989
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40432998
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.
0
 

Author Comment

by:yguyon28
ID: 40433091
BusinessUnit is a INT
IWMCUW is a String
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40433113
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

0
 

Author Comment

by:yguyon28
ID: 40433123
Thank you Jim I understand the concept in all that just not sure how to modify the script.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40433179
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.
0
 

Author Comment

by:yguyon28
ID: 40433352
ah I got you no luck tks you for the help
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question