Solved

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

Posted on 2014-11-10
9
246 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now