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
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)

Open in new window



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)

Open in new window

View Error Messagebu.txt
Mark01Asked:
Who is Participating?
 
Richard QuadlingSenior Software DeveloperCommented:
Try ...

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,
	a.tTime

Open in new window


Note the change in the GROUP BY clause. The way the time is rendered doesn't need to be a part of the aggregation.

In fact, aggregating on a time is really odd unless you are sure that multiple entries happened on the same date time.

A few questions/pointers about the query so far.

1 - As you aren't using anything from the duration table, so do you need it? If not, drop the table and filter using
WHERE b.DurationID IS NOT NULL

Open in new window

.
2 - Does tblTimes contain unique times only? If so, you can aggregate on the main tables TimeID.
3 - Do you need to aggregate? Do you have multiple entries for a single date time? If not, don't aggregate at all.

I wonder if the following query is better suited to your needs.

SELECT
	a.tDate,
	a.tTime,
	CASE WHEN b.Object_1 IS NULL THEN 0 ELSE 1 END Dist1,
	CASE WHEN b.Object_2 IS NULL THEN 0 ELSE 1 END Dist2,
	CASE WHEN b.Object_3 IS NULL THEN 0 ELSE 1 END Dist3
FROM
	dbo.tblObjects AS b
	INNER JOIN
	dbo.tblTimes AS a ON a.TimeID = b.TimeID
WHERE
	b.DurationID IS NOT NULL

Open in new window

0
 
Richard QuadlingSenior Software DeveloperCommented:
What type is a.tTime? And does it need to be converted?

You don't seem to be referencing tblDurations. Can this be removed?
0
 
Daniel WilsonCommented:
The 2 code blocks are equivalent.  The latter is a preferred syntax.

The problem involves the conversion.  What is the actual data type of a.tTime?  Is it actually a TIME type?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Daniel WilsonCommented:
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:
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)

Open in new window


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)

Open in new window


Also, are you running SQL Server 2008 or SQL Server 2008 Release 2?  There may be a difference in how they handle this.
0
 
Richard QuadlingSenior Software DeveloperCommented:
If you use CAST(), don't forget to use it in the GROUP BY clause also.
0
 
Anthony PerkinsCommented:
After you have used (or attempted to use) the VIEW designer more than a couple of times you will discover its flaws and stop using it entirely.  It is a total waste of time.  Use the query window instead.
0
 
Mark01Author Commented:
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.
Database DiagramDurations TableObjects TableTimes Table
0
 
Mark01Author Commented:
Here are the errors I got when I tried Daniel Wilson's code:

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)

Open in new window


Error
View 1 Error
0
 
Mark01Author Commented:
Here are the errors I got when I tried Daniel Wilson's code:

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)

Open in new window


Error
View 2 Error
0
 
Mark01Author Commented:
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.
0
 
Richard QuadlingSenior Software DeveloperCommented:
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.
0
 
Mark01Author Commented:
Thank you, Anthony Perkins, Daniel Wilson and RQuadling.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.