Solved

Query to View Error

Posted on 2014-04-05
12
452 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 40

Expert Comment

by:RQuadling
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
 
LVL 40

Expert Comment

by:RQuadling
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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:
RQuadling 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:RQuadling
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now