Solved

Query to View Error

Posted on 2014-04-05
12
492 Views
Last Modified: 2014-04-07
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
0
Comment
Question by:Mark01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 39980620
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
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 50 total points
ID: 39980624
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39980664
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 39981230
If you use CAST(), don't forget to use it in the GROUP BY clause also.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39981581
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
 

Author Comment

by:Mark01
ID: 39981583
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
 

Author Comment

by:Mark01
ID: 39981588
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
 

Author Comment

by:Mark01
ID: 39981590
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
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 400 total points
ID: 39981844
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
 

Author Comment

by:Mark01
ID: 39981950
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
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 39982120
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
 

Author Comment

by:Mark01
ID: 39984027
Thank you, Anthony Perkins, Daniel Wilson and RQuadling.
0

Featured Post

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question