SQL Bug... Can't figure out - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Bug... Can't figure out

Author

Topic

mariokartman
Starting Member

5 Posts

Posted - 2009-11-25 : 01:02:05

I'm trying to run a script to create a derived table which basically just pulls data from other tables into one neat place.

I'm having a weird issue where it's giving me an error. Here's what I've boiled down to be the problem:


SELECT DISTINCT
  START.ID 
  ,END.LINE / 2 as LINE
  ,GetDate() as DATE
  ,NEXT_ID
  ,START.TIME as START_INSTANT
  ,END.TIME as END_INSTANT
  ,CASE WHEN (SELECT TOP 1 VALUE
              FROM DATA_VALUES
	      WHERE DATA_VALUES.TIME >= START.TIME
		 AND DATA_VALUES.ID = 206
		 AND DATA_VALUES.VALUE IS NOT NULL
		 AND DATA_VALUES.TIME <= END.TIME) = 'Yes' then 'Y' 
 ELSE 			    
     CASE WHEN (SELECT TOP 1 VALUE
                FROM DATA_VALUES
	        WHERE DATA_VALUES.TIME >= START.TIME
		 AND DATA_VALUES.ID = 206
		 AND DATA_VALUES.VALUE IS NOT NULL
		 AND DATA_VALUES.TIME <= END.TIME) = 'No' then 'N' ELSE 'Unknown'
	END
END
		as COLUMN_1
		
,(SELECT SUM(CONVERT(FLOAT,DATA_VALUES.VALUE))
  FROM DATA_VALUES 
  WHERE DATA_VALUES.TIME >= START.TIME
		        AND DATA_VALUES.ID = 533
		        AND DATA_VALUES.VALUE IS NOT NULL
		        AND DATA_VALUES.TIME <= END.TIME) as COLUMN_2

FROM START
 INNER JOIN END on START.ID = END.ID 
	  
WHERE START.TYPE = 340
 AND END.TYPE = 341
 AND START.LINE % 2 = 1
 AND END.LINE = START.LINE + 1

The problem occurs when I run this query. If I run both parts of the query separately, it works fine and returns correct values. But when I try to run this query as it is right now, it returns the error:

Msg 8114, Level 16, State 5, Line 59
Error converting data type varchar to float.

I think I've narrowed it down to the sum(convert(float,DATA_VALUES.VALUE))) because it's the only varchar that I'm working with where I'm trying to convert to a float. When I run that query separately, it returns with the numbers .26, .25, and .17. I even added "ISNUMERIC(DATA_VALUES.VALUE) = 1" to the where clause of the subquery (I got that from one of your other threads).

I only started getting this problem after I nested the CASE statements. If I only do 1 level of case statement in the first subquery, then it works fine.

Any ideas would definitely help.

mariokartman
Starting Member

5 Posts

Posted - 2009-11-25 : 01:03:07

Oh I guess I should have said that I'm having problems when both of the last two subqueries are in the query... the nested CASE one, and the sum(convert()) one.

Oh, and also don't mind that one of my table's names is END. I changed the table names because this is something for work and I didn't want to use the real table names.

mariokartman
Starting Member

5 Posts

Posted - 2009-11-25 : 10:42:35

This is the data I get back when I run the queries separately, I just want to be able to run them together



 ID   LINE             DATE                     START_INSTANT        END_INSTANT        COLUMN_1
-----------------------------------------------------------------------------------------------
1300	1	2009-11-25 09:43:01.147	2009-11-17 11:28:00.000	2009-11-17 11:34:53.000	Y
1302	1	2009-11-25 09:43:01.147	2009-11-18 01:00:00.000	2009-11-18 01:14:00.000	Unknown
1303	1	2009-11-25 09:43:01.147	2009-11-18 13:00:00.000	2009-11-18 13:03:37.000	Unknown
1304	1	2009-11-25 09:43:01.147	2009-11-19 16:02:00.000	2009-11-19 16:04:00.000	Unknown
1305	1	2009-11-25 09:43:01.147	2009-11-19 16:15:00.000	2009-11-19 16:16:39.000	Unknown
1306	1	2009-11-25 09:43:01.147	2009-11-20 10:51:07.000	2009-11-20 11:00:00.000	Unknown
1306	2	2009-11-25 09:43:01.147	2009-11-20 16:30:43.000	2009-11-20 16:32:17.000	Unknown
1307	1	2009-11-25 09:43:01.147	2009-11-19 23:59:00.000	2009-11-20 01:00:00.000	Unknown
1308	1	2009-11-25 09:43:01.147	2009-11-22 23:00:00.000	2009-11-22 23:46:19.000	Unknown
1309	1	2009-11-25 09:43:01.147	2009-11-22 23:30:00.000	2009-11-22 23:45:00.000	Unknown


COLUMN_2
-----
NULL
NULL
NULL
NULL
NULL
NULL
0.68
NULL
NULL
NULL

mariokartman
Starting Member

5 Posts

Posted - 2009-11-25 : 13:22:48

Ok I've analyzed the problem further... the subquery


,(SELECT SUM(CONVERT(FLOAT,DATA_VALUES.VALUE))
  FROM DATA_VALUES 
  WHERE DATA_VALUES.TIME >= START.TIME
		        AND DATA_VALUES.ID = 533
		        AND DATA_VALUES.VALUE IS NOT NULL
		        AND DATA_VALUES.TIME <= END.TIME) as COLUMN_2

is actually bringing back values that aren't numeric. Although everything with DATA_VALUES.ID = 533 is either NULL or numeric. Is there any way to get that WHERE clause to execute before it tries to do the SUM? I tried putting the subquery inside the SUM parentheses, but it told me that that wasn't allowed (something about cannot execute aggregate function with a subquery inside of it)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-25 : 13:56:06

If ID is equal to 206 then you are comparing DATA_VALUES.VALUE with 'Yes' or 'No'.
Maybe there is your problem.
Is DATA_VALUES.VALUE a varchar, sure?
OR
Is at least one DATA_VALUES.VALUE something like '0,68' instead of '0.68'?

No, you're never too old to Yak'n'Roll if you're too young to die.

mariokartman
Starting Member

5 Posts

Posted - 2009-11-25 : 14:56:01

DATA_VALUES is a varchar, and it's a big bunch of a mess of varchars. There's a messy mix of numeric and non-numeric varchars. The ones that are supposed to be numeric are definitely numeric (no 0,68 instead of 0.68). The ones that aren't supposed to be numeric are definitely not numeric (like 'Yes').

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources