Solved

View Error

Posted on 2014-04-16
11
364 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 34

Accepted Solution

by:
James0628 earned 250 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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read 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…
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

21 Experts available now in Live!

Get 1:1 Help Now