Solved

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

Posted on 2014-11-10
9
269 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help separating values from a column and creating a new record 6 69
Database Owner 3 45
MySQL programmer starter 25 64
MSSQL Convert Char to Date Time 5 36
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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