July 11, 2011 at 6:47 pm
Below are two similar queries. One works. One returns an error message that I need some explanation. ANY INSIGHT AS TO WHY THE SECOND DOESN'T WORK WOULD BE APPRECIATED.
'Value' and 'DateTime' are column names.
WORKING Query
-------------------
USE RUNTIME
GO
DECLARE @Tag_Namevarchar(128),
@StartDatedatetime,
@EndDatedatetime;
SET @StartDate = '2011-03-12 0:00';
SET @EndDate = '2011-03-12 23:59';
SET @Tag_Name = 'CB01_SS1_351S_1P6_MWh';
SELECT ROUND(MAX(Value),1)
FROM History
WHERE TagName = @Tag_Name
AND DateTime >= @StartDate
AND DateTime <= @EndDate;
GO
_____________________________________________________________
NOT WORKING Query
------------------
USE RUNTIME
GO
DECLARE @Tag_Namevarchar(128),
@StartDatedatetime,
@EndDatedatetime,
@Result1float;
SET @StartDate = '2011-03-12 0:00';
SET @EndDate = '2011-03-12 23:59';
SET @Tag_Name = 'CB01_SS1_351S_1P6_MWh';
SET @Result1 = (SELECT ROUND(MAX(Value),1)
FROM History
WHERE TagName = @Tag_Name
AND DateTime >= @StartDate
AND DateTime <= @EndDate);
SELECT @Result1
GO
ERROR MSG
Msg 7339, Level 16, State 1, Line 11
OLE DB provider 'DBSQL' for linked server 'DBSQL' returned invalid data for column '[DBSQL].[Runtime].[dbo].[History].Value'.
July 11, 2011 at 7:28 pm
I'd guess it has to do with using the float datatype for @Result1. Try it with DECIMAL and let us know if it works.
http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx
FLOAT is not a precision data type, and even though you round the value when setting the variable, it could end up returning more decimal places than your frontend is expecting or can handle.
July 12, 2011 at 7:14 am
Yeah I'd agree with the DECIMAL value. I'm assuming its a typo, but you used SET instead of SELECT in your second statement. It should read:
SELECT @Result1 = (SELECT ROUND(MAX(Value),1)
FROM History
WHERE TagName = @Tag_Name
AND DateTime >= @StartDate
AND DateTime <= @EndDate);
Also, your query is dangerously constructed. There's no guarantee that you will have only one return value, and in the case where there are multiple returned values, the value assigned to @Result1 will be the last record in the recordset returned. However, you have no ORDER BY clause, which means that value is volatile, and you could end up with a different result each time you run it.
You should always have queries which assign values to variables, be written with an explicit ORDER BY clause, as well as a SELECT TOP 1, even if you are sure that there will only be one value returned. No point in having to go back and spend hours investigating a bug later on in the future when the code changes.
July 12, 2011 at 7:23 am
kramaswamy (7/12/2011)
SELECT @Result1 = (SELECT ROUND(MAX(Value),1)
FROM History
WHERE TagName = @Tag_Name
AND DateTime >= @StartDate
AND DateTime <= @EndDate);
Also, your query is dangerously constructed. There's no guarantee that you will have only one return value, and in the case where there are multiple returned values, the value assigned to @Result1 will be the last record in the recordset returned. However, you have no ORDER BY clause, which means that value is volatile, and you could end up with a different result each time you run it.
You should always have queries which assign values to variables, be written with an explicit ORDER BY clause, as well as a SELECT TOP 1, even if you are sure that there will only be one value returned. No point in having to go back and spend hours investigating a bug later on in the future when the code changes.
He is assigning this to the result of an aggregate. You can't get multiple results for MAX().
To the OP, you really should consider NOT using keywords as column names. Not only are they incredibly confusing they are totally ambiguous. What is DateTime in a record? Is that the date the record was created, last edited, last viewed?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2011 at 7:25 am
Oops. Right. I knew that 😡
July 12, 2011 at 7:27 am
kramaswamy (7/12/2011)
Oops. Right. I knew that 😡
Can't tell you how many time I have done that myself. I just wanted to clarify to avoid confusion for the OP. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2011 at 7:33 am
I agree regarding the column names. I didn't create table - it was autogenerated by the app I am using. Its an industrial HMI in a production plant. The DateTime column stores the most recent time the record Value changed. Thanks for the response
July 12, 2011 at 7:35 am
I hear you on automated tools. The authors of said tool should be ashamed!!! That is just plain lazy and causes their clients untold amount of anguish.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply