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

x
?
Solved

View Error

Posted on 2014-04-16
11
Medium Priority
?
397 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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