Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query to View Error

Posted on 2014-04-05
12
Medium Priority
?
525 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 200 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 200 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 1600 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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