View Error

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
Mark01Asked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
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
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
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
 
Paul JacksonSoftware EngineerCommented:
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
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!

 
Paul JacksonSoftware EngineerCommented:
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
 
Mark01Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
Mark01Author Commented:
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
 
Mark01Author Commented:
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
 
Mark01Author Commented:
James, your code does function properly in the View window. I made some sort of error this afternoon. Thank you, James and Paul.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
 
Anthony PerkinsCommented:
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
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.