HELP! Error converting data type varchar to float

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)

HELP! Error converting data type varchar to float

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-06-23 : 13:31:37

HELP !!!

Ladies & Gents

I use this stored proceedure every week and suddenly it stopped
working. I was hoping someone to point me in the right direction in correcting the problem. When executing the sp. I get the following Error... Error converting data type varchar to float.

Here is my stored proceedure:

Alter Procedure sp_project_schedule_test
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As

SELECT Project.Project_ID, substring(Project.Project_Name,1,40) as Project_Name,
Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME AS TERR, Project.Status,
SUM(Activities.Hours_Work) AS Hours_Work,
SUM(Activities.Hours_Travel) AS Hours_Travel,

'Total_Hours' =

SUM(case when

(Activities.Hours_Work + Activities.Hours_Travel) > 0

then (Activities.Hours_Work + Activities.Hours_Travel)

else 0 end),

MIN(CONVERT(datetime, CONVERT(varchar,
DATEPART(m, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(d, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(yy,
Activities.Time_Stamp)))) AS First_Work,
MAX(CONVERT(datetime, CONVERT(varchar, DATEPART(m,
Activities.Time_Stamp)) + '/' + CONVERT(varchar, DATEPART(d,
Activities.Time_Stamp)) + '/' + CONVERT(varchar,
DATEPART(yy, Activities.Time_Stamp)))) AS Last_Work,

'Quoted_Hours' =

case when

(MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours))) > 0

then (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours)))

else 0 end, max(Project.sched_complete_date) as sched_complete_date

FROM dbo.Activities INNER JOIN
dbo.Tasks ON
dbo.Activities.Task_ID = dbo.Tasks.Task_ID INNER JOIN
dbo.Services ON
dbo.Tasks.Service_ID = dbo.Services.ServiceID RIGHT OUTER JOIN
dbo.Project INNER JOIN
dbo.vw_project_last_activity_date ON
dbo.Project.Project_ID = dbo.vw_project_last_activity_date.Project_ID
ON
dbo.Services.ProjectID = dbo.Project.Project_ID LEFT OUTER JOIN
ADI.dbo.[SALES GROUPS] ON
dbo.Project.In_Sls_No = ADI.dbo.[SALES GROUPS].TERR#

WHERE (dbo.Project.Status = N'open') AND dbo.Project.Standard = 'no'
OR
(NOT (dbo.Project.Status LIKE N'cancel%')) AND
(dbo.vw_project_last_activity_date.Last_Activity > { fn NOW()
} - 30) AND dbo.Project.Standard = 'no'

GROUP BY dbo.Project.Project_ID, dbo.Project.Project_Name,
dbo.Project.Status, dbo.Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME
HAVING (NOT (dbo.Project.Project_Name LIKE N'mis test%')) AND
(NOT (dbo.Project.Status = N'CANCELED')) AND
(NOT (dbo.Project.Project_ID = '88888')) AND
(NOT (dbo.Project.Project_ID = '100254'))

order by Project.Project_ID desc

Any help would be greatly appreciated.
Thanks in advance
pharoah34

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-23 : 13:37:23

Well don't panic ... approach the problem methodically. I see at least two places where you convert to a float:

quote:
(MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours))) > 0

then (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours)))

So ... take out those conversions and examine the rowset. You may find the IsNumeric() function helpful if the rowset is quite large. See which rows have a non-numeric value and go from there.

Edited by - setbasedisthetruepath on 06/23/2003 13:37:57

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-23 : 13:44:09

Damn...it's one of those miracle things again....

My money is on...

You (or someone) changed something

What's the ddl for the table?

Are you storing numeric info in varchar columns?

If so, then you will always run that risk.

You'll then need check if the data ISNUMERIC first with a case and pass out 0 if it's not, or make it part of the predicate...

There are no miracles...

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-23 : 13:47:17

Miracles ???? What the hell are you talking about?

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-23 : 14:12:07

quote:

I use this stored proceedure every week and suddenly (miracously) it stopped
working.

That's what the hell I'm talking about....

Brett

8-)

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-06-24 : 12:32:26

Hello setbasedisthetruepath

Thanks so much for your help and suggestion.
However when I attempt to remove the FLOAT
statements (as suggested)I am not able to save the
stored proceedure and test it..

Please HELP, Anyone Please
pharoah35

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-24 : 12:44:59

Post the DDL for your table project table. Most likley they are defined as a char datatype.

And, further, most likely, you now have (you guessed it) char data in that column.

Do this:

SELECT * FROM Project
WHERE ISNUMERIC(Quoted_Eng_Hours)=0
OR ISNUMERIC(Quoted_Tech_Hours)=0

If you get ANY rows back from that, you've got char data...

Brett

8-)

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-06-24 : 14:26:14

Hello X002548

First, Thank you so much for your help concerning this matter.
It is greatly appreciated.

I executed the statement in QA (Query Analyzer) as instructed
and yes it did in fact return all of the rows of data. So what
do you suggest in terms of correcting the stored proceedure so that it executes?

Again, Thank you so much
pharoah35

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-24 : 16:32:36

Well what's in the columns? It's going to be some type of character data, which for you using it as a numeric field will cause you your problem...

Your going to have to ask yourself what your suppose to do when 1 byt not the other is not numeric.

If you use the following (replace it in your SELECT), you should no longer get your error:


CASE WHEN ISNUMERIC(Project.Quoted_Tech_Hours) + ISNUMERIC(Project.Quoted_Eng_Hours) = 2 THEN
	CASE WHEN (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours) 
		     + CONVERT(FLOAT, Project.Quoted_Eng_Hours))) > 0 THEN

		  (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours) 
		     + CONVERT(FLOAT, Project.Quoted_Eng_Hours))) 
		   ELSE 0
	END
     ELSE 0
END

Brett

8-)

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-06-25 : 08:10:06

Good morning Brett

Again, Thank you so much for your greatly appreciated assistance concerning this problem. What would people like me do without good people like you.

I added your suggested statements into my stored proceedure. However it won't let me save and run the sp. It generated the following error:
ADO Error: Line 41: Incorrect syntax near ','.

Here is the stored proceedure with your additions. I hope I added them correctly.
______________________________________________________________________
Alter Procedure sp_project_schedule_test
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As

SELECT Project.Project_ID, substring(Project.Project_Name,1,40) as Project_Name,
Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME AS TERR, Project.Status,
SUM(Activities.Hours_Work) AS Hours_Work,
SUM(Activities.Hours_Travel) AS Hours_Travel,

'Total_Hours' =

SUM(case when

(Activities.Hours_Work + Activities.Hours_Travel) > 0

then (Activities.Hours_Work + Activities.Hours_Travel)

else 0 end),

MIN(CONVERT(datetime, CONVERT(varchar,
DATEPART(m, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(d, Activities.Time_Stamp))
+ '/' + CONVERT(varchar, DATEPART(yy,
Activities.Time_Stamp)))) AS First_Work,
MAX(CONVERT(datetime, CONVERT(varchar, DATEPART(m,
Activities.Time_Stamp)) + '/' + CONVERT(varchar, DATEPART(d,
Activities.Time_Stamp)) + '/' + CONVERT(varchar,
DATEPART(yy, Activities.Time_Stamp)))) AS Last_Work,

'Quoted_Hours' =

case when

CASE WHEN ISNUMERIC(Project.Quoted_Tech_Hours) + ISNUMERIC(Project.Quoted_Eng_Hours) = 2 THEN
CASE WHEN (MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours))) > 0 THEN

(MAX(CONVERT(FLOAT, Project.Quoted_Tech_Hours)
+ CONVERT(FLOAT, Project.Quoted_Eng_Hours)))

else 0 end, max(Project.sched_complete_date) as sched_complete_date

FROM dbo.Activities INNER JOIN
dbo.Tasks ON
dbo.Activities.Task_ID = dbo.Tasks.Task_ID INNER JOIN
dbo.Services ON
dbo.Tasks.Service_ID = dbo.Services.ServiceID RIGHT OUTER JOIN
dbo.Project INNER JOIN
dbo.vw_project_last_activity_date ON
dbo.Project.Project_ID = dbo.vw_project_last_activity_date.Project_ID
ON
dbo.Services.ProjectID = dbo.Project.Project_ID LEFT OUTER JOIN
ADI.dbo.[SALES GROUPS] ON
dbo.Project.In_Sls_No = ADI.dbo.[SALES GROUPS].TERR#

WHERE (dbo.Project.Status = N'open') AND dbo.Project.Standard = 'no'
OR
(NOT (dbo.Project.Status LIKE N'cancel%')) AND
(dbo.vw_project_last_activity_date.Last_Activity > { fn NOW()
} - 30) AND dbo.Project.Standard = 'no'

GROUP BY dbo.Project.Project_ID, dbo.Project.Project_Name,
dbo.Project.Status, dbo.Project.Customer_Name,
ADI.dbo.[SALES GROUPS].NAME
HAVING (NOT (dbo.Project.Project_Name LIKE N'mis test%')) AND
(NOT (dbo.Project.Status = N'CANCELED')) AND
(NOT (dbo.Project.Project_ID = '88888')) AND
(NOT (dbo.Project.Project_ID = '100254'))

order by Project.Project_ID desc
______________________________________________________________________

Thanks again so very much.
Eric.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources