DERIVED TABLE???QUERY?????
-
OK...I am trying to create something that would be a derived table I guess. The query is:
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
dbo.LOCATION.lLOC AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
WHERE pTYPE <> 'FIRE'
ORDER BY dbo.SENSOR.sBASETAG + '_' + 'alrmtrig'
I tried using a view, but the program I am using to access the DB didn't work well with views. I think that it would actually repopulate each time it accessed which in this case is > 15,000 consecutively. I think that a ?derived? table is more what I need. I need something that is tied to the actual table so that the data is updated in one table only but pulled into the new table.
Can someone please help me?????
-
Errrrr....
I'm not certain what exactly you are trying to do here. I don't see a derived table in your query. Or, as far as I can tell, a view. And, I'm not sure what you mean by "pulled into the new table."
I think what you're saying is that you need a way to persist this query, and, for some reason, a view won't work. Will a stored procedure work?
If not, you can create a holding table. You will need to either write a stored procedure to populate it when the program first needs to access it, or write triggers in your base tables to update the holding table.
I think you may also need to re-evaluate the program you're using, if it can't work with a view. And needs to run the query that often. It seems to me like you're trying to work blindfolded with one arm tied behind your back.
-
That is exactly how I felt...
This is what i came up with. It works, but I am not sure (not being very adept at SQL Server) if there is not a more efficient way to achieve the same results.
CREATE PROCEDURE spALARMS
AS
DROP TABLE tALARMS
CREATE TABLE tALARMS (AlrmBaseTag varchar(8000), AlrmLoc nvarchar(255), AlrmPanel int, AlrmType nvarchar(25))
INSERT INTO tALARMS (AlrmBaseTag, AlrmLoc, AlrmPanel, AlrmType)
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
dbo.LOCATION.lLOC AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
UPDATE tALARMS
SET AlrmLoc= AlrmType
WHERE AlrmType = 'FIRE'
GO
-
Okay.
First of all, don't DROP and CREATE the table. That's a lot of overhead. TRUNCATE the table instead.
Second, you should roll the functionality of your UPDATE statement into your INSERT statement.
So:
CREATE PROCEDURE spALARMS
AS
TRUNCATE TABLE tALARMS -- You may want to check to make sure the table exists here
INSERT INTO tALARMS (AlrmBaseTag, AlrmLoc, AlrmPanel, AlrmType)
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
(CASE WHEN dbo.PANEL.pTYPE = 'FIRE' THEN 'FIRE' ELSE dbo.LOCATION.lLOC END) AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
GO
-
So I understand that you want to have a table that contains a persistent view of your data. I would expect that your client-side app can work with views, but you might need to specify the view's name properly. With INSTEAD OF triggers and indexed views you can almost have a view work as a table in SQL 2000 & up
If you could get your client-app DB API to work with views then I would suggest you have appropriate indices on your tables (particularly on the join conditions and your filtering conditions) and see how that goes. If insufficient, try an indexed view. Alternatively you could have yout tAlarms table and keep it in sync with your other tables via triggers - as you add / update / delete rows you make appropriate changes in tAlarms table.
-
This works, but I know it can be cleaner. I wasn't sure how to check for existing table and create or truncate.
CREATE PROCEDURE spALARMS
AS
IF OBJECT_ID('tALARMS') IS NULL
BEGIN
CREATE TABLE tALARMS (AlrmBaseTag varchar(8000), AlrmLoc nvarchar(255), AlrmPanel int, AlrmType nvarchar(25))
INSERT INTO tALARMS (AlrmBaseTag, AlrmLoc, AlrmPanel, AlrmType)
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
(CASE WHEN dbo.PANEL.pTYPE = 'FIRE' THEN 'FIRE' ELSE dbo.LOCATION.lLOC END) AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
ORDER BY AlrmBaseTag
END
ELSE
BEGIN
TRUNCATE TABLE tALARMS
INSERT INTO tALARMS (AlrmBaseTag, AlrmLoc, AlrmPanel, AlrmType)
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
(CASE WHEN dbo.PANEL.pTYPE = 'FIRE' THEN 'FIRE' ELSE dbo.LOCATION.lLOC END) AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
ORDER BY AlrmBaseTag
END
GO
-
That will work, but can be awkward to read. Separate your IF statement out into its own block.
IF OBJECT_ID('tALARMS') IS NULL
BEGIN
CREATE TABLE tALARMS (AlrmBaseTag varchar(8000), AlrmLoc nvarchar(255), AlrmPanel int, AlrmType nvarchar(25))
END
ELSE
BEGIN
TRUNCATE TABLE tALARMS
END
BEGIN
INSERT INTO tALARMS (AlrmBaseTag, AlrmLoc, AlrmPanel, AlrmType)
SELECT dbo.SENSOR.sBASETAG + '_' + 'alrmtrig' AS AlrmBaseTag,
(CASE WHEN dbo.PANEL.pTYPE = 'FIRE' THEN 'FIRE' ELSE dbo.LOCATION.lLOC END) AS AlrmLoc,
dbo.SENSOR.sPANEL AS AlrmPanel,
dbo.PANEL.pTYPE AS AlrmType
FROM dbo.SENSOR INNER JOIN
dbo.PANEL ON dbo.SENSOR.sPANEL = dbo.PANEL.pPANEL INNER JOIN
dbo.LOCATION ON dbo.PANEL.pLOCATION = dbo.LOCATION.lLOCATION
END
Also, don't put the ORDER BY in your INSERT statement. Rows are not actually stored in tables in any particular order. Adding the ORDER BY just creates needless overhead.
-
Thanks sooo much! You have been a life saver!
-
No problem.
Keep in mind that is still a VERY sub-optimal solution. For a number of reasons. If I were you, I'd seriously talk to whoever wrote that program and see what other solutions can be worked out.
-
When I run the sp like this it creates the table but never insert data into it. ????
Never mind...I took out the begins and ends and it works fine thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply