I have a table structured like this:
sensorId int, Value int, obDate datetime
in SQL Server 2008 Express.
I want to get the latest value for each sensor, so I've created a View Like this:
CREATE VIEW [dbo].[LAT_POLDATA]
AS (
SELECT A.sensorId, A.obDate, A.Value
FROM [Met].[dbo].[POLDATA] AS A
INNER JOIN
(SELECT sensorId, MAX(obDate) AS obDate
FROM [Met].[dbo].[POLDATA]
GROUP BY sensorId) AS B ON A.sensorId = B.sensorId
AND A.obDate = B.obDate
)
Now If I query Select * from [dbo].[LAT_POLDATA]
I get values when I do this in Microsoft SQL Server Management Studio, but I don't see this view under the Views
in my database.
If I query it from a ASP.net Service, the query fails and throws an error:
System.Data.SqlClient.SqlException: Invalid object name 'LAT_POLDATA'
Where is the view? Why don't I see it SSMS?
In Management Studio please try adding your preferred database name and execute it.
use [yourDatabaseName]
Select * from [dbo].[LAT_POLDATA]
If that gives you an error that view is not in your database
Then follow this
USE [yourDatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[LAT_POLDATA] AS (
SELECT A.sensorId , A.obDate , A.Value
FROM [Met].[dbo].[POLDATA] AS A
INNER JOIN (
SELECT sensorId , MAX(obDate) AS obDate
FROM [Met].[dbo].[POLDATA]
GROUP BY sensorId
) AS B
ON A.sensorId = B.sensorId
AND A.obDate = B.obDate
)
GO