Hello.
I’m trying to setup a property formula in and I’m having trouble. I’m
trying to get the sum of all issues and errors that were flagged during a
validation step. This system distinguishes between an “issue” and an
“error”, and they are currently stored in two distinct tables, but I’d like
to count them all for a property in this class, which summarizes the
validation process.
I have a little trouble mapping the formula; advice would be appreciated.
The class’s main table is tblValidationJobs. The formula has 3 other tables
come into play: tblValidatioResults, tblValidationIssues and
tblValidationErrors. One job has many results, and each result may have 0
or many issues and 0 or many errors. The goal of the formula is, for the
validation job, to provide the total number of issues + errors that occurred
during the job.
Here is part of the class mapping:
<class name="ValidationResultSummary" table="tblValidationJobs"
mutable="false">
<id name="Id" column="validationJobId">
<generator class="hilo" />
</id>
<!-- Other properties -->
<property name="IssuesRaisedCount" formula=
"(SELECT COUNT(*)
FROM (
SELECT tblValidationIssues.validationResultId
FROM tblValidationResults INNER JOIN tblValidationIssues
ON
tblValidationResults.validationResultId=tblValidationIssues.validationResult
Id
WHERE tblValidationResults.validationJobId=validationJobId
UNION ALL
SELECT tblValidationErrors.validationResultId
FROM tblValidationResults INNER JOIN tblValidationErrors
ON
tblValidationResults.validationResultId=tblValidationErrors.validationResult
Id
WHERE tblValidationResults.validationJobId=validationJobId
) IssuesAndErrors)" />
</class>
It generates the following SQL (properties other than the id and the formula
removed for brevity, the bit in red is the problem):
SELECT validation0_.validationJobId as validati1_99_0_, (
SELECT COUNT(*)
FROM (
SELECT tblValidationIssues.validationResultId
FROM tblValidationResults INNER JOIN tblValidationIssues
ON
tblValidationResults.validationResultId=tblValidationIssues.validationResult
Id
WHERE
tblValidationResults.validationJobId=validation0_.validationJobId
UNION ALL
SELECT tblValidationErrors.validationResultId
FROM tblValidationResults INNER JOIN tblValidationErrors
ON
tblValidationResults.validationResultId=tblValidationErrors.validationResult
Id
WHERE
tblValidationResults.validationJobId=validation0_.validationJobId
) validation0_.IssuesAndErrors) as formula3_0_
FROM DBName.dbo.tblValidationJobs validation0_
WHERE validation0_.validationJobId=?
The formula’s table alias is being prepended with the parent query’s table
name, which is causing a syntax error. Can anyone suggest a means for me to
set this up?
Regards,
Remi.
--
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.
