?
Solved

View Error

Posted on 2014-04-16
11
Medium Priority
?
393 Views
Last Modified: 2014-04-20
I have a database that contains information about object vibrations. Each object vibrates a certain number of times per minute. I have a query that displays the correct data about each object. However, it does not display the duration for each object. I have a View that does not display the correct data about each object.

Both the query and View must display the date, time, duration and vibrations per second for each object.

QUERY
SELECT	a.tDate
,	convert(time,a.tTime) tTime
,	Dist1		=	MAX(ISNULL( b.Object_1, 0 ))
,	Dist2		=	MAX(ISNULL( b.Object_2, 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


Query Result
VIEW
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


Erroneous View Result
Object 1 vibrated 5 times in 1 second. Object 2 vibrated one time 2 seconds. Object 3 vibrated 9 times in 3 seconds. I’m running SQL Server 2008 R2. Screenshots of the results of the Query and the View are attached. Also attached are screenshots of the database diagram and tables.

The database backup is also attached. 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.

Also attached is an image showing the data in the database.

Please help me rewrite the View so that it returns accurate data as well as the duration for each object.
Database TablesDatabase DiagramData in Databasebu.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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 1000 total points
ID: 40005851
First to correct your view the column Dist2 in the Select clause is referencing the wrong column b.Object_1 rather than b.Object_2 use :
SELECT
	a.tDate,
	a.tTime,
	MAX(ISNULL(b.Object_1, 0)) AS Dist1,
	MAX(ISNULL(b.Object_2, 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

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40005855
Adding duration to the query, you have already added the join to the durations table so you just need to add the duration column to the select clause :

SELECT	a.tDate,	
                convert(time,a.tTime) tTime,
        	Dist1		=	MAX(ISNULL( b.Object_1, 0 )),
         	Dist2		=	MAX(ISNULL( b.Object_2, 0 )),
        	Dist3		=	MAX(ISNULL( b.Object_3, 0 )),
                c.Duration
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

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40005856
same for the view :

SELECT
	a.tDate,
	a.tTime,
	MAX(ISNULL(b.Object_1, 0)) AS Dist1,
	MAX(ISNULL(b.Object_2, 0)) AS Dist2,
	MAX(ISNULL(b.Object_3, 0)) AS Dist3,
        c.Duration
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

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Mark01
ID: 40007883
Paul, your query (ID: 40005851) returns the correct data.


Your query with the duration field (ID: 40005855) returned the error shown below.

ERROR:

Msg 8120, Level 16, State 1, Line 6
Column 'tblDurations.Duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


For your View (ID: 40005856), I pasted your code 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 the error show in the attached screenshot.
View Error
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40008377
just add duration to the group by clause :

SELECT	a.tDate,	
                convert(time,a.tTime) tTime,
        	Dist1		=	MAX(ISNULL( b.Object_1, 0 )),
         	Dist2		=	MAX(ISNULL( b.Object_2, 0 )),
        	Dist3		=	MAX(ISNULL( b.Object_3, 0 )),
                c.Duration
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), c.Duration

Open in new window


SELECT
	a.tDate,
	a.tTime,
	MAX(ISNULL(b.Object_1, 0)) AS Dist1,
	MAX(ISNULL(b.Object_2, 0)) AS Dist2,
	MAX(ISNULL(b.Object_3, 0)) AS Dist3,
        c.Duration
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,
        c.Duration

Open in new window

0
 

Author Comment

by:Mark01
ID: 40009083
Paul, both your Query and View (ID: 40008377) return six rows of data. Several rows have values of zero. In order to create a report, I have to have three rows of data including the duration.

Your query (ID: 40005851) returns the correct data in the desired three rows except without the duration. The attached screenshot shows the desired result. It is a  modified image of the result of your query (ID: 40005851).

I am going to use Crystal Reports to design a report that shows something to the effect of:

2-02-2014 at 9:00 a.m. Object 1: 1 vibration per second (Duration: 1 second)


If it's too difficult to rewrite the query to return only three rows of data, I'll close the question and give you the points.
Desired Result
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 40010225
It wasn't clear (at least to me) that you wanted 3 separate durations, one for each "object".  In retrospect, it makes sense, given your data.  I just didn't get that from the things that you'd posted.  FWIW, I think part of the problem goes back to your questionable table structure (as mentioned in another recent question that you posted), with 3 "object" columns in tblObjects.

 Given your queries, I am assuming that, on any given row, only one of the 3 "object" columns in tblObjects (Object_1, Object_2 or Object_3) will have a value, and the other 2 columns will be null.

 If that's correct, you could try the following.  It tries to use CASE statements to select the duration for each "object", based on whether or not the "object" column is null.

 James

SELECT
	a.tDate,
	a.tTime,
	MAX(ISNULL(b.Object_1, 0)) AS Dist1,

        MAX(
            CASE WHEN b.Object_1 IS NULL THEN 0
            ELSE c.Duration
            END
        ) AS Dur1,

	MAX(ISNULL(b.Object_2, 0)) AS Dist2,

        MAX(
            CASE WHEN b.Object_2 IS NULL THEN 0
            ELSE c.Duration
            END
        ) AS Dur2,

	MAX(ISNULL(b.Object_3, 0)) AS Dist3,

        MAX(
            CASE WHEN b.Object_3 IS NULL THEN 0
            ELSE c.Duration
            END
        ) AS Dur3

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

0
 

Author Comment

by:Mark01
ID: 40010872
James, your query returns the correct result. Is there an easy way to convert it to a View? I pasted your code 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 the errors shown in the attached screenshots.
View ErrorView Error #2
0
 

Author Comment

by:Mark01
ID: 40010928
James, your code does function properly in the View window. I made some sort of error this afternoon. Thank you, James and Paul.
0
 
LVL 35

Expert Comment

by:James0628
ID: 40010950
You're welcome.  Glad I could help.

 James
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40011389
After I clicked on the table diagram in the View window, SQL Server changed the code. The View will not execute and I get the errors shown in the attached screenshots.
You will find that the VIEW Designer is somewhere between flawed and useless and will only let you use the most basic queries.  You are far better off using the query window directly to create the VIEW.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

765 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