Error converting data type varchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error converting data type varchar to numeric

Author

Topic

sqlbee
Starting Member

11 Posts

Posted - 2003-09-04 : 12:51:43

this statement keeps on giving me this error.
the data i have is chp_hour is <20,<30,<40 etc.
stand time is numeric.

SELECT EMPLOYEE.EMPLOYEE, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_INIT,
EMPLOYEE.EMP_STATUS, EMPLOYEE.FICA_NBR, EMPLOYEE.STAND_HOURS, PAPOSITION.R_POSITION,
PAPOSITION.DESCRIPTION, PAPOSITION.POSIT_STATUS,hrEmployerData.CHP_Hours
FROM EMPLOYEE INNER JOIN
PAPOSITION ON EMPLOYEE.COMPANY = PAPOSITION.COMPANY AND
EMPLOYEE.PROCESS_LEVEL = PAPOSITION.PROCESS_LEVEL AND
Left(EMPLOYEE.EMP_STATUS,1)='A'
INNER JOIN hrEmployerData ON PAPOSITION.PROCESS_LEVEL = hrEmployerData.PROCESS_LEVEL
WHERE ((PAPOSITION.POSIT_STATUS=1 AND EMPLOYEE.STAND_HOURS<REPLACE(hrEmployerData.CHP_Hours,'>','')) OR
EMPLOYEE.STAND_HOURS<20)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-04 : 12:59:17

Your code:

SELECT	EMPLOYEE.EMPLOYEE
	, EMPLOYEE.LAST_NAME
	, EMPLOYEE.FIRST_NAME
	, EMPLOYEE.MIDDLE_INIT
	, EMPLOYEE.EMP_STATUS
	, EMPLOYEE.FICA_NBR
	, EMPLOYEE.STAND_HOURS
	, PAPOSITION.R_POSITION
	, PAPOSITION.DESCRIPTION
	, PAPOSITION.POSIT_STATUS
	, hrEmployerData.CHP_Hours 

FROM	EMPLOYEE INNER JOIN PAPOSITION 
		ON 	EMPLOYEE.COMPANY = PAPOSITION.COMPANY
		AND 	EMPLOYEE.PROCESS_LEVEL = PAPOSITION.PROCESS_LEVEL
		AND	Left(EMPLOYEE.EMP_STATUS,1)='A'
 
	INNER JOIN hrEmployerData 
		ON PAPOSITION.PROCESS_LEVEL = hrEmployerData.PROCESS_LEVEL 

WHERE ((PAPOSITION.POSIT_STATUS=1 AND EMPLOYEE.STAND_HOURS<REPLACE(hrEmployerData.CHP_Hours,'>','')) OR
EMPLOYEE.STAND_HOURS<20)

The error is here:

EMPLOYEE.STAND_HOURS<REPLACE(hrEmployerData.CHP_Hours,'>','')

comparing a varchar field to a numeric

try this

EMPLOYEE.STAND_HOURS< CONVERT(integer, (ltrim(rtrim(REPLACE(hrEmployerData.CHP_Hours,'>',''))))) --check my parentheses

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-04 : 13:00:04

You need to convert it:

EMPLOYEE.STAND_HOURS < CONVERT(NUMERIC(16,1), REPLACE(hrEmployerData.CHP_Hours,'>','')))

Why does your data have ">" in it?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 13:01:02

how about...


    SELECT  EMPLOYEE.EMPLOYEE
	  , EMPLOYEE.LAST_NAME
	  , EMPLOYEE.FIRST_NAME
	  , EMPLOYEE.MIDDLE_INIT
	  , EMPLOYEE.EMP_STATUS
	  , EMPLOYEE.FICA_NBR
	  , EMPLOYEE.STAND_HOURS
	  , PAPOSITION.R_POSITION
	  , PAPOSITION.DESCRIPTION
	  , PAPOSITION.POSIT_STATUS
	  ,hrEmployerData.CHP_Hours 
      FROM EMPLOYEE 
INNER JOIN PAPOSITION 
	ON EMPLOYEE.COMPANY = PAPOSITION.COMPANY 
       AND EMPLOYEE.PROCESS_LEVEL = PAPOSITION.PROCESS_LEVEL 
       AND Left(EMPLOYEE.EMP_STATUS,1)='A' 
INNER JOIN hrEmployerData 
	ON PAPOSITION.PROCESS_LEVEL = hrEmployerData.PROCESS_LEVEL 
     WHERE ((PAPOSITION.POSIT_STATUS=1 
       		AND EMPLOYEE.STAND_HOURS < CONVERT(integer,REPLACE(hrEmployerData.CHP_Hours,'>',''))) 
		 OR EMPLOYEE.STAND_HOURS < 20)

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 13:03:15

Response flood...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources