January 7, 2004 at 10:00 am
In MS SQL 2000 how should a save the following query so that the results from the SELECT statement can be accessed via a Database View. Also is possible please include the required syntax for the view.
SET NOCOUNT ON
DROP TABLE #RsvInd
DROP TABLE #RsvExp
CREATE TABLE #RsvInd
(
ID INT,
SumRsvInd DECIMAL(18,2)
);
CREATE TABLE #RsvExp
(
ID INT,
SumRsvExp DECIMAL(18,2)
);
INSERT INTO #RsvInd
SELECT ID, Sum(IsNull([RSV_INDEMNITY_AMT],0)) AS SumRsvInd
FROM dbo.CLTRANSACTION INNER JOIN CODE_VALUE ON CLTRANSACTION.TRANSACTION_CD = CODE_VALUE.CODE_VALUE_ID
GROUP BY CLTRANSACTION.ID;
INSERT INTO #RsvExp
SELECT ID, Sum(IsNull([RSV_EXPENSE1_AMT],0)) AS SumRsvExp
FROM dbo.CLTRANSACTION INNER JOIN CODE_VALUE ON CLTRANSACTION.TRANSACTION_CD = CODE_VALUE.CODE_VALUE_ID
WHERE (((CODE_VALUE.USER_CODE)<>'V'))
GROUP BY CLTRANSACTION.ID;
SELECT CL.ID, #RsvInd.SumRsvInd, #RsvExp.SumRsvExp
FROM dbo.CL CL
INNER JOIN #RsvInd ON CL.ID = #RsvInd.ID
INNER JOIN #RsvExp ON CL.ID = #RsvExp.ID
ORDER BY CL.ID
DROP TABLE #RsvInd
DROP TABLE #RsvExp
Go
January 7, 2004 at 11:35 am
Perhaps something like this:
SELECT cl.ID, i.SumRsvInd, e.SumRsvExp
FROM dbo.CL cl
JOIN
(SELECT t.ID, SUM(c.Rsv_Indemnity_Amt) SumRsvInd
FROM dbo.ClTransaction t JOIN Code_Value c ON t.Transaction_Cd = c.Code_Value_Id
GROUP BY t.ID) i ON i.ID = cl.ID
JOIN
(SELECT t.ID, SUM(c.Rsv_Expense_Amt) SumRsvExp
FROM dbo.ClTransaction t JOIN Code_Value c ON t.Transaction_Cd = c.Code_Value_Id
WHERE c.User_Code <> 'V'
GROUP BY t.ID) e ON e.ID = cl.ID
The subqueries are aliased as derived tables i and e. You don't need ISNULL() in your expressions as the SUM() aggregate function ignores null values.
--Jonathan
January 7, 2004 at 11:37 am
You can't create views on temporary tables...you'll have to either
a) wrap up the code above inside a stored procedure and execute the stored procedure to get the results..
or
b)re-write the query in the form of a select statement and use that in a view...
this might be the query that you can use in the view (and this query also might be totally wrong)
CREATE VIEW dbo.MyView
AS
SELECT CL.ID,Tab1.SumRsvInd,Tab2.RsvExp FROM dbo.CL CL INNER JOIN
(
SELECT ID, Sum(IsNull([RSV_INDEMNITY_AMT],0)) AS SumRsvInd
FROM dbo.CLTRANSACTION INNER JOIN CODE_VALUE ON CLTRANSACTION.TRANSACTION_CD = CODE_VALUE.CODE_VALUE_ID
GROUP BY CLTRANSACTION.ID
)As Tab1
ON
Tab1.ID = CL.ID
INNER JOIN
(
SELECT ID, Sum(IsNull([RSV_EXPENSE1_AMT],0)) AS SumRsvExp
FROM dbo.CLTRANSACTION INNER JOIN CODE_VALUE ON CLTRANSACTION.TRANSACTION_CD = CODE_VALUE.CODE_VALUE_ID
WHERE (((CODE_VALUE.USER_CODE)<>'V'))
GROUP BY CLTRANSACTION.ID
)As Tab2
ON
Tab2.ID = CL.ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply