Databases
--
Questions
--
Followers
Top Experts
Query to View Error
I converted a query to a view by pasting the code from the query window into the new View window. After I clicked on the table diagram in the View window, SQL Server changed the code. The View will not execute and I get an error (see the screenshot).
I had to change the extension of the backup file to .txt in order to upload it to EE. Just change the extension back to .bu.
How do I resolve the error?
QUERY
VIEW
bu.txt
I had to change the extension of the backup file to .txt in order to upload it to EE. Just change the extension back to .bu.
How do I resolve the error?
QUERY
SELECT a.tDate
, convert(time,a.tTime) tTime
, Dist1 = MAX(ISNULL( b.Object_1, 0 ))
, Dist2 = MAX(ISNULL( b.Object_1, 0 ))
, Dist3 = MAX(ISNULL( b.Object_3, 0 ))
FROM tblTimes a
JOIN tblObjects b ON a.TimeID = b.TimeID
JOIN tblDurations c ON b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)VIEW
SELECT a.tDate, CONVERT(time, a.tTime) AS tTime, MAX(ISNULL(b.Object_1, 0)) AS Dist1, MAX(ISNULL(b.Object_1, 0)) AS Dist2, MAX(ISNULL(b.Object_3, 0)) AS Dist3
FROM dbo.tblTimes AS a INNER JOIN
dbo.tblObjects AS b ON a.TimeID = b.TimeID INNER JOIN
dbo.tblDurations AS c ON b.DurationID = c.DurationID
GROUP BY a.tDate, CONVERT(time, a.tTime)Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
What type is a.tTime? And does it need to be converted?
You don't seem to be referencing tblDurations. Can this be removed?
You don't seem to be referencing tblDurations. Can this be removed?
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Comparing your error to this:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f987779d-3d3f-4aec-bf51-e9335f9012e2/error-in-view-when-using-convertdategetdate?forum=transactsql
If a.tTime is a Time field, try without conversion:
Otherwise, see how a CAST serves you instead of a Convert.
Also, are you running SQL Server 2008 or SQL Server 2008 Release 2? Â There may be a difference in how they handle this.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f987779d-3d3f-4aec-bf51-e9335f9012e2/error-in-view-when-using-convertdategetdate?forum=transactsql
If a.tTime is a Time field, try without conversion:
SELECT a.tDate, a.tTime, MAX(ISNULL(b.Object_1, 0)) AS Dist1, MAX(ISNULL(b.Object_1, 0)) AS Dist2, MAX(ISNULL(b.Object_3, 0)) AS Dist3
FROM dbo.tblTimes AS a INNER JOIN
dbo.tblObjects AS b ON a.TimeID = b.TimeID INNER JOIN
dbo.tblDurations AS c ON b.DurationID = c.DurationID
GROUP BY a.tDate, CONVERT(time, a.tTime)Otherwise, see how a CAST serves you instead of a Convert.
SELECT a.tDate, CAST( a.tTime as Time) AS tTime, MAX(ISNULL(b.Object_1, 0)) AS Dist1, MAX(ISNULL(b.Object_1, 0)) AS Dist2, MAX(ISNULL(b.Object_3, 0)) AS Dist3
FROM dbo.tblTimes AS a INNER JOIN
dbo.tblObjects AS b ON a.TimeID = b.TimeID INNER JOIN
dbo.tblDurations AS c ON b.DurationID = c.DurationID
GROUP BY a.tDate, CONVERT(time, a.tTime)Also, are you running SQL Server 2008 or SQL Server 2008 Release 2? Â There may be a difference in how they handle this.
If you use CAST(), don't forget to use it in the GROUP BY clause also.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
I'll submit multiple responses because my previous lengthy response apparently did not upload to your server. I'm running SQL Server 2008 Release 2. Screenshots of the database diagram and tables are attached.
Here are the errors I got when I tried Daniel Wilson's code:
VIEW 1:
Error
VIEW 1:
SELECT a.tDate, a.tTime, MAX(ISNULL(b.Object_1, 0)) AS Dist1, MAX(ISNULL(b.Object_1, 0)) AS Dist2, MAX(ISNULL(b.Object_3, 0)) AS Dist3
FROM dbo.tblTimes AS a INNER JOIN
dbo.tblObjects AS b ON a.TimeID = b.TimeID INNER JOIN
dbo.tblDurations AS c ON b.DurationID = c.DurationID
GROUP BY a.tDate, CONVERT(time, a.tTime)Error
Here are the errors I got when I tried Daniel Wilson's code:
VIEW 2:
Error
VIEW 2:
SELECT a.tDate, CAST( a.tTime as Time) AS tTime, MAX(ISNULL(b.Object_1, 0)) AS Dist1, MAX(ISNULL(b.Object_1, 0)) AS Dist2, MAX(ISNULL(b.Object_3, 0)) AS Dist3
FROM dbo.tblTimes AS a INNER JOIN
dbo.tblObjects AS b ON a.TimeID = b.TimeID INNER JOIN
dbo.tblDurations AS c ON b.DurationID = c.DurationID
GROUP BY a.tDate, CONVERT(time, a.tTime)Error
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
RQuadling, your View does execute without any errors. I will be using the Durations table for other reports. I appreciate your comments about the need for aggregating on a time.
Thank you for the points.
Using the Durations table for other views is fine, but the view you were creating didn't use it, so, once created, it was redundant.
Anyway, thanks again. Hope the view provides the right results.
Using the Durations table for other views is fine, but the view you were creating didn't use it, so, once created, it was redundant.
Anyway, thanks again. Hope the view provides the right results.
Thank you, Anthony Perkins, Daniel Wilson and RQuadling.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Databases
--
Questions
--
Followers
Top Experts
Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.