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)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Thank you, Anthony Perkins, Daniel Wilson and RQuadling.
You don't seem to be referencing tblDurations. Can this be removed?