Hello All,
I usually develop the middle-tier and front-end of our ASP.NET - C# -
nHibernate - SQL Server project but I am venturing more and more into
the back-end. I am not new to development but I am relatively new to
all things database and so I would like some advice regarding a
relatively complex query.
I have the following query, and it works in SQL Server 2005 Management
Studio.The parts of interest are the first INNER JOIN clause and the
Select statement. Our database has a TestSetResults table with a
primary key of TestSetResultID, a TestCases table with a primary key
of TestCaseID and a TestCaseResults table with keys of TestSetResultID
and TestCaseID. In this way a Test Set Result record is associated
with many Test Case Result records. The purpose of the query is to get
back a maximum of 50 TestSetResult records along with the number of
tests completed (a count of the associated TestCaseResult records) and
the number of test failures (a count of all the associated
TestCaseResult records with a status of "failure") for each test run
result. (A colleague provided me with this query, and I'm not entirely
sure how it works).
SELECT TOP (50) {TSR.*}, {t1.*}, {t2.*}, {t3.*}, {t4.*}, {t5.*},
TestCounts.TestsCompleted, TestCounts.NumFailures
FROM TestSetResults AS TSR
INNER JOIN (SELECT TCR.TestSetResultID, COUNT (TCR.TestCaseID) AS
TestsCompleted,
SUM(CASE WHEN TCR.TestCaseStatus ==
'failure' THEN 1 ELSE 0 END) AS NumFailures
FROM TestCaseResults TCR
GROUP BY TCR.TestSetResultID)
AS TestCounts
ON TestCounts.TestSetResultID =TSR. TestSetResultID
INNER JOIN table1 AS t1 ON TSR.t1ID = t1.ID
INNER JOIN table2 AS t2 ON TSR.t2ID = t2.ID
INNER JOIN table3 AS t3 ON t2.t3ID = t3.ID
INNER JOIN table4 AS t4 ON TSR.t4ID = t4.ID
INNER JOIN table5 AS t5 ON t4.t5ID = t5.ID
WHERE pID IN ('...')
ORDER BY TSR.StartDateTime DESC
The trouble I am having is that I can't get the values of
TestCounts.TestsCompleted and TestCounts.NumFailures from the query
result. I suspect that there is a far better way of doing this by
using the SUM and COUNT aggregate functions in the Select clause.
The code that I am using to perform this query looks something like
this...
if (this.session.IsOpen)
{
using (ITransaction tx = this.session.BeginTransaction())
{
ISQLQuery nHibernateQuery = this.session.CreateSQLQuery
(this.Query.Sql.Statement);
AddEntitiesToQuery(ref nHibernateQuery);
this.query.Result = nHibernateQuery.List();
tx.Commit();
}
}
I would be very grateful is somebody could explain how I should get
the TestCounts values from the query result.
Thanks in advance,
Mikey
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
