Solved

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

Posted on 2014-11-10
9
264 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
  • 5
  • 4
9 Comments
 
LVL 65

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 65

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 65

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 65

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

861 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